# 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

#1 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 •

#2
August 2, 2011 at 23:03:33
 Thanks DerbyDad03,I am using Excel 2003I 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 