Count multiple formula.. but with a twist!

**mcget** · 07-27-2009, 07:33 AM

Hi Guys,

Im having a huge problem.. here goes;

I have a spreadsheet which is populated by data on another spreadsheet. Therefore this entire spreadsheet contains formulae, rather than values.

i want to do a multiple count like this;

=SUM((f2:f2000=”PHARMACY”)*(h2:h2000=”Yes”))

I have tried this as well as entering it as an array formula. I have also tried adding a column in with only number 1's, so if the above criteria is met, it could count the 1's in this column... but still no luck..

I think the problem im having is that the spreadsheet is formulae, and the count functions are not picking up the values?

Does anyone have any solutions as i am not going mental!

Thanks

Paul

**Palmetto** · 07-27-2009, 07:41 AM

You need to use SUMPRODUCT

=SUMPRODUCT((f2:f2000=”PHARMACY”)*(h2:h2000=”Yes”))

**mcget** · 07-27-2009, 07:57 AM

Thanks Palmetto, but his formula does not work!

Ive even tried it on a simple 3x5 table, to no avail!

**martindwilson** · 07-27-2009, 08:17 AM

what do you mean a table?

**DonkeyOte** · 07-27-2009, 08:22 AM

check for trailing spaces etc... ie does this work ?

=SUMPRODUCT(--(TRIM(f2:f2000)=”PHARMACY”),--(TRIM(h2:h2000)=”Yes”))

if it does either/or/and values in F & H contain leading/trailing spaces - ie PHARMACY does not exist per se, it may be "Pharmacy " or " Pharmacy" etc...