Complex count if ?!

**_Lewis** · 06-14-2011, 08:04 AM

Hi all,

Please can somebody help me with something similar to a “countif” statement.

Basically I’m counting data in column B that is equal to one. - =COUNTIF(B2:B94,"1")

I also have a column A with different codes: FA , YE , YD , BS

I would like my count statement to cross relate with column A so to produce a “count” the data will have to be equal to 1 column B and equal to FA in column A.

FA 1
YE 1
BS 2
BS 3
FA 2

Is that possible ?

Thank you

**zbor** · 06-14-2011, 08:09 AM

Try:

=COUNTIF($B$2:$B2,$B2)

and pull down

**daddylonglegs** · 06-14-2011, 08:09 AM

Try SUMPRODUCT like this

=SUMPRODUCT((B2:B94=1)*(A2:A94="FA"))

Note: I took out the quotes around the 1 - probably not needed if column B data is numeric

**_Lewis** · 06-14-2011, 08:11 AM

Sorry i'm not amazing at excel by anymeans, what is that forumula calculating ?

i was thinking perhaps it would have to be ?

=COUNTIF(B2:B96,"1")=COUNTIF(A2:A95,"FA")

but this returns the value FALSE

**_Lewis** · 06-14-2011, 08:17 AM

Originally Posted by daddylonglegs

Try SUMPRODUCT like this

=SUMPRODUCT((B2:B94=1)*(A2:A94="FA"))

Note: I took out the quotes around the 1 - probably not needed if column B data is numeric

Thats fantastic exactly what i was looking for 5* for you