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?

=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.

--
Regards,
Tom Ogilvy


"caddly" wrote:

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?

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" wrote:

=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.

--
Regards,
Tom Ogilvy


"caddly" wrote:

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?

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.

--
Regards,
Tom Ogilvy


"caddly" wrote:

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" wrote:

=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.

--
Regards,
Tom Ogilvy


"caddly" wrote:

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?

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" wrote:

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.

--
Regards,
Tom Ogilvy


"caddly" wrote:

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" wrote:

=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.

--
Regards,
Tom Ogilvy


"caddly" wrote:

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?

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

--
Regards,
Tom Ogilvy


"caddly" wrote:

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" wrote:

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.

--
Regards,
Tom Ogilvy


"caddly" wrote:

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" wrote:

=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.

--
Regards,
Tom Ogilvy


"caddly" wrote:

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?