Countif compares to Countifs

[kje.1953]

How can I use a count function in excel 2003 that does the same thing as
countifs in Excel 2007?

I created this multi criteria in Excel 2007 but it won’t work in Excel 2003
as the rest of the team don’t have the same version and it converts to
#NAME?.

I am trying to count the number of times where a particular criteria has
been used e.g. 12 Mth where there is a date in column AC

=_xlfn.COUNTIFS($AB$3:$AB$150,"12 mth",$AC$3:$AC$150,">0")

How can I use mutilpe criteria in Excel 2003 to do the same thing?

 

[Sheeloo]

Use

=SUMPRODUCT(--($AB$3:$AB$150="12 mth"),--($AC$3:$AC$150>0))

 

[kje.1953]

Thank you for getting back to me so soon. I tried it but it essentially just
counted the number of 'products" which Countif does already.

What I am trying to do is where there is a date (as they accepted the offer)
to look at the product & give me a count.
Products are in colum AB, date/acceptance is in column AC

I got the results when I used countifs but somehow I am not getting it this
time. Can you help me?

 

[Sheeloo]

It IS counting the products where they match "12 mth" AND where corresponding
cell in AC is not blank (">0")

Test it out after deleting everything from Col AC then entering dates one by
one...
You will see that the count starts at 1 and increases as you enter the
dates...

I tested again and it is working...

It it does not work then pl. put X in all blank cells in AC and change the
condition to <>"X" and see if it works...

Do you have formulas in AC?

 

[T. Valko]

=SUMPRODUCT(--($AB$3:$AB$150="12 mth"),--($AC$3:$AC$150>0))

Try changing >0 to ISNUMBER:

=SUMPRODUCT(--($AB$3:$AB$150="12 mth"),--(ISNUMBER($AC$3:$AC$150)))

 

[Shane Devenshire]

Hi,

Lets go back to your first question, how can us use a COUNT function in 2003
to do the same thing as COUNTIFS in 2007. You can but you need to use the
DCOUNT or DCOUNTA functions. The COUNT, COUNTIF, COUNTBLANK function can't
duplicate the functionality of COUNTIF or there wouldn't be a COUNTIFS
function, it would be redundant.

It gives you a NAME error message in 2003 because that function doesn't
exist in 2003, it is one of the 12 new functions introduced in 2007.

COUNTIFS($AB$3:$AB$150,"12 mth",$AC$3:$AC$150,">0")

This formula appears to be counting all the rows which contain 12 mth in
column AB and are greater than 0 in column AC.

To do this with DCOUNTA assume you have titles on row 2. In some empty
cells enter the exact titles found in AB2 and AC2, I'm going to call those
Month and Amount for the sake of this example. Below these to titles, which
I will assume you are going to put in the empty cell AM1 and AN1 enter your
two criteria, the result would look like this:
AM AN
1 Month Amount
2 12 mth >0

Now enter the following formula in an empty cell:

=DCOUNTA(AB2:AC150,1,AM1:AN2)

You can also use a SUMPRODUCT function like the one previously suggested:
=SUMPRODUCT(--(AB3:AB150="12 mth"),--(AC3:AC150>0))

Now let's suppose this doesn't work, then I must ask what is 12 mth? It
sounds like you are refering to a DATE but you are showing us a TEXT entry.
If it is a legal Excel date then the DCOUNTA and SUMPRODUCT functions will
fail. Both of these functions would return 0.

If the entries really are 12 mth text then both functions will return all
the rows that match on both condtions at the same time. They will not count
the items if they only match one criterial, however, neither will COUNTIFS.

If this helps, please click the Yes button

cheers,
Shane Devenshire

 

[kje.1953]

Thank you so much everyone for your assistance. I have used the
=SUMPRODUCT(--($AB$3:$AB$150="12 mth"),--(ISNUMBER($AC$3:$AC$150)))
which worked best on the totals.

You guys are all abosultely amazing. I have learnt so much.