COUNIFS

[Guest]

At home I have Excel 2007 but at work we use Excel 2003.
Excel 2007 now has a new function called COUNTIFS. (Counts the number of
cells within a range that meet multiple criteria)
Obviously when I send the file back to work it no longer works properly.
Is there a way to reproduce the function in Excel 2003?

 

[Guest]

=SUMPRODUCT(--(rng1="criteria1"),--(rng2="criteria2"),--(rng3="criteria3"))

 

[Guest]

I imagine Teethless mama has answered your question, if not the please post
the COUNTIFS formula you're using

 

[Guest]

Thank you for your replies but you credit me with too much knowledge.
I understand the “range†and “criteria†but what does the -- stand for?
I have a table similar to below.

A:A B:B C:C
102 Y 1
103 Y 1
116 Y N
117 Y N
102 N N
103 N 1
116 Y N
117 Y N

I need two formulas which I understand will be very similar.
The first one is where I want to find how many times 102 appears in A:A and
where the 102 also has a Y in that row in B:B. The second formula is the same
but has the addition that C:C also has a 1 in the same row.

When I use COUNTIFS the first formula is
=COUNTIFS(April!A:A,B4,April!B:B,"y") the B4 equals the
102 value.

Thanks in advance

Larry

 

[Roger Govier]

Hi Larry

=SUMPRODUCT(--(rng1="criteria1"),--(rng2="criteria2"),--(rng3="criteria3"))
The formula as above provided by TM, just needs substituting with your
ranges and criteria.

=SUMPRODUCT(--(April!$A$1:$A$1000=102),--(April!$B$1:$B$1000="y"),--(April!$C$1:$C$1000=1))
for the three criteria situation.

=SUMPRODUCT(--(April!$A$1:$A$1000=102),--(April!$B$1:$B$1000="y"))
for the two criteria result.

Note, other tan in XL2007, you cannot use whole columns as ranges within
Sumproduct.
Change the ranges to those sufficient to match your data.

The -- (double unary minus) is used to create the True/False response from
the tests, to 1/0 which are them summed by Sumproduct to provide the answer.

Use this method in XL2007, as it will also work in earlier versions of XL,
whereas Countifs in XL2007 specific.

 

[Guest]

Thank you very much, this now works perfectly for me.