need formula to count data by type

**endz0926** · 07-16-2009, 03:17 AM

Hi anyone who can help me with this query.

I need to count EC1 (refer to data tab - column A) with 100.00% score (refer to Data tab - column AA:Score).

Please refer to attachment for the sample data.

I need anyone's input - thank you.

endz0926

**DonkeyOte** · 07-16-2009, 03:21 AM

Without using helpers you are left with SUMPRODUCT

=SUMPRODUCT(--(Data!$A$1:$A$100="EC1"),--(Data!$AA$1:$AA$100=1))

The reference to EC1 can be a cell containing EC1.

You may also want to consider using a Pivot Table if you wish to repeat the function over and over as Sumproducts are quite "expensive".