COUNTIFS is a new function available in Excel 2007. This has proved to be very helpful and it is always a problem if you switch back and forth between the two versions. Well, here is an equivalent for COUNTIFS that you can use until your company decides to spend those extra bucks and get everyone on to version 2007 :)

Let’s assume the data is as below.

These will be the equivalents in 2003, using SUMPRODUCT.

COUNTIFS

=SUMPRODUCT((A2:A10=E5)*(B2:B10=F5))

Where E5 has the counter and F5 has the sales-person. The same can be extended for a SUMIFS as below.

SUMIFS

=SUMPRODUCT((A2:A10=E5)*(B2:B10=F5)*(C2:C10))

Next post, we’ll talk about two functions that are not in any version of Excel yet – MAXIF and MINIF.

Thank-you, this was very helpful

@Josh,

Thank you for the feedback. Good to know it helped.

Sajin: I am trying to ge tmy Excel 2003 to give me counts for customers whose spending is greater than x but less than y so I can do some fast segmentation. It doesn’t like the arguments for the greater than and less than, so I had to do it myself. Can this be done in 2003 or do I have to stand on my head until 2007 gets implemented?

@Scott,

For getting a count of spending above 1000 and less than 4000, we can do as below.

=COUNTIF(B:B,">1000") - COUNTIF(B:B,">=4000")

Hope that helps.

I received a #N/A after the range gets pass a high number. Is there a way around it?

NVM… the arrays were not the same size; therefore, i received the error message

Extremely clever, handy, and helpful formula. Many thanks!

works fine in 2007. waiting to check it on 2003. shd work though. thnxxx a ton.

Thank you, it’s very helpful~

Hi,

Basic on the above table figure, If i wanna to ask for the total bill amount for Counter “A” with Person “Y” = (2+5) = 7. is there any formula to count?

Regards

it’s great…. very helful.. thank you

extremely helpful site. thanks and many thanks again

For the last one month i am searching for a solution for this problem. Just now i have solved through your guidance. Verry Verry thanks. Is there any guide in Pivot Table then please update me.

THANKS SO MUCH>>>>> simplest and best explanation on the web! So appreciate it!

Thank you. I was searching for such a solution. It really works well

Hey guys im having a huge issue with converting back to excel 2003 any help would be appreciated,

there are 2 columns that i need to filter to count.

at the moment i use

=COUNTIFS(WORKSHEET!$G:$G,REPORTS!K$2,WORKSHEET!$D:$D,REPORTS!$J3)

worksheet has the data on it, reports has the reports.

Worksheet column G has the date in it. the date is in the format MMM, YY.

Worksheet colum D has the component in it. Ie Torque converter

i need to count how many “Torque Converters” there are in May 2012.