Excel formula needed to match two criteria

[Attleborough]

I need a formula that will count the number of rows where two fixed criteria are located - not one or the other. Ideally needs to ignore blanks.

So if column A has colours and column B has numbers - I'd like to know the number of rows where Blue & 4 both feature - not blue on its own or 4 on its own.

I've put a few rows of data in a sheet and tried a couple of formula without success -

=SUM(($A$7:$A$14="blue")*($B$7:$B$14="4"))

& also

=SUMPRODUCT(--(A7:A14="blue")*(B7:B14="4"))

I'm using Excel 2003

Can anyone help, please?

A.

 

[E. Buygum]

Not elegant but: create a third column (C) and enter the formula =IF(AND(A7="blue",B7=4),TRUE,FALSE) into C7. Copy this formula into C8:C14. Enter the formula =COUNTIF(C7:C14,"TRUE") into another cell somewhere.

 

[shugie]

You might get further using the formulae 'COUNT' and 'IF' I suspect, assuming 2003 supports these as later versions does?

 

[GSmonkey]

You need to learn how to use a PivotTable. Example attached. Good luck!

 

[repomanbe]

Very simple

=COUNTIFS(A7:A14,"blue",B7:B14,4)



Add $ back into cell references if you need them to be fixed references

 

[Attleborough]

Thanks for the suggestions, I will give them a try.

GSmonkey - yes I do but attempts so far usually end with with bad language.

Appreciate you all taking time to help.

A.

 

[Paul Wakefield]

Very simple

=COUNTIFS(A7:A14,"blue",B7:B14,4)



Add $ back into cell references if you need them to be fixed references

Unfortunately not available in Excel 2003. XL2007 onwards only IIRC.

 

[Paul Wakefield]

Your Sumproduct formula should work - try taking the quotes away from the number.


i.e.
=SUMPRODUCT(--(A7:A14="blue")*(B7:B14=4))

 

[Attleborough]

Thanks all, in the end I found that putting the search criteria in a cell solved the probleme.g.

=SUMPRODUCT((C7:C15=E5)*(D715=F5))

Thanks again to all with suggestions.

A.