excel 2003 count if multiple criteria

Microsoft Excel 2003 (full product)
August 2, 2011 at 16:52:43
Specs: Windows XP
Hi i am trying to do a count if for multiple criteria but am running into problems.

I have a spreadsheet with multiple tabs and am running queries on a summary tab. On the tabs I am trying to report on the first column has the department name and the second column has a description of which some items contain the letters BB at the end.

I can use the COUNTIF function to get all the items ending in BB with the following =COUNTIF('May-11'!B:B,"* BB") and I can report on the count of departments (regardsless of ending in BB) with the following =COUNTIF('May-11'!A:A,A2) where A2 is referencing a list of the departments on the summary page.

In summary I would like to get any item in column B ending in BB and split this up by department in column A.

See More: excel 2003 count if multiple criteria

Report •

August 2, 2011 at 18:27:23
What version of Excel are you using?

In 2007 and beyond you can use the COUNTIFS function:

=COUNTIFS('May-11'!B:B, "* BB", 'May-11'!A:A, A2)

In any version you can use SUMPRODUCT:

=SUMPRODUCT((RIGHT('May-11'!B:B,3)=" BB") * ('May-11'!A:A=A2))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 2, 2011 at 23:03:33
Thanks DerbyDad03,

I am using Excel 2003

I tried the formula you suggested but I am getting the #NUM! error. I tested the formula on a single cell basis with =SUMPRODUCT((RIGHT('May-11'!B2,3)=" BB") * ('May-11'!A2=$A$2)) which worked but as a range I could not get it to work.

Ok I finally worked out what was wrong. You cannot use entire columns when you use this so the formula below worked perfectly.

=SUMPRODUCT((RIGHT('May-11'!$B$2:$B$1000,3)=" BB")*('May-11'!$A$2:$A$1000=A2)).

Report •

Related Solutions

Ask Question