COUNTIFS and SUMIFS in Excel 2003

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.
countifs-example

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.

16 thoughts on “COUNTIFS and SUMIFS in Excel 2003

  1. 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?

    1. @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.

  2. 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

  3. 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.

  4. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">