I hope someone can help me with a formula

[caddly]

I have a spreadsheet set up and need a formula to count the number of entries
in a row IF another row's criteria matches.
ie: COUNT(IF(B3:IV3,"Excellent"),IF(B1:IV1,"A07C,A06C,A18C)
The problem I'm encountering is that the 'count' formula does't seem to work
with multiple criteria...does anyone have any suggestions?

 

[Tom Ogilvy]

=Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A07C"))+Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A06C"))+Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A18C"))

would be one guess at what you want.

 

[caddly]

Hi there...I tried your suggestion, but it isn't working either? Any other
suggestions?
I know it's difficult when you don't have the sheet in front of you, but any
suggestions would be very helpful. This is a statistical report I'm trying to
formulate with much criteria involved, as you can see.

=SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A07C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A06C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A09C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A27C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A28C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A18C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A12C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A10C"))

 

[Tom Ogilvy]

Just saying it doesn't work doesn't help much since it works for what I wrote
it for/how I interpreted what you wanted.

Your formula should give you the count of columns from B to IV that have
Excellent in row 3 and any of those 4 digit codes in row 1. If that
description matches what you want, and you are not getting that from the
formula, time to look at the data and make sure it is clean.

 

[caddly]

Thanks so much for your help thus far. Can you tell me what you mean by
making sure my data is clean? (I'm a novice at this...trying to learn)

 

[Tom Ogilvy]

If there are blanks or something in your cells so that Excellent is really
"Excellent " as an example.

Send me a sample workbook with an explanation of what you expect the results
to be and why and I will see what I can come up with (e-mail address removed)

 

[caddly]

Oh my gosh...I figured it out, thanks to you...I was omitting the 'sheet1!'
reference and wasn't getting a count, but I inserted that in front and have
the correct count now!

Thank you so much for your patience!