COUNIFS

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Larry Byrne - 29 Sep 2007 18:42 GMT

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?

Reply to this Message

Teethless mama - 29 Sep 2007 18:51 GMT

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

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

Reply to this Message

daddylonglegs - 30 Sep 2007 00:01 GMT

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

> =SUMPRODUCT(--(rng1="criteria1"),--(rng2="criteria2"),--(rng3="criteria3"))
>
[quoted text clipped - 3 lines]
> > 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?

Reply to this Message

Larry Byrne - 30 Sep 2007 13:34 GMT

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

> I imagine Teethless mama has answered your question, if not the please post
> the COUNTIFS formula you're using
[quoted text clipped - 6 lines]
> > > 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?

Reply to this Message

Roger Govier - 30 Sep 2007 15:42 GMT

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.

Signature

Regards
Roger Govier

> 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?
[quoted text clipped - 39 lines]
>> > > properly.
>> > > Is there a way to reproduce the function in Excel 2003?

Reply to this Message

Larry Byrne - 07 Oct 2007 17:45 GMT

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

> Hi Larry
>
[quoted text clipped - 60 lines]
> >> > > properly.
> >> > > Is there a way to reproduce the function in Excel 2003?

Reply to this Message