COUNTIF function for multiple criteria

[yorkshirelass]

I hope someone can help me. I devised a s/sheet in Excel 2007 using the
COUNTIFS function. However, not all my colleagues like 2007 and have
reverted back to 2003. I have tried to redesign the formula using
various variations of COUNTIF but I can't get it to return the correct
value. The formula I use in Excel 2007 is:

=COUNTIFS(L2:L123,D2123,SUCCESSFUL,P2123,2007)

Basically if the range within column D=Successful and the range within
column P=2007, I want it to count them.

Should I be using something else i.e. SUMPRODUCT?

Urgent help much appreciated.

 

[Bernard Liengme]

=SUMPRODUCT(--(D2123=SUCCESSFUL),--(P2123=2007),L2:L123)
or, if the word Successful is text and not a Named cell
=SUMPRODUCT(--(D2123="SUCCESSFUL"),--(P2123=2007),L2:L123)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

 

[yorkshirelass]

THIS DIDN'T WORK I'M AFRAID - IT RETURNED A RIDICULOUS NUMBER. THERE
ARE 15 MATCHES OF 'SUCCESSFUL' AND '2007' - ANY MORE IDEAS? I'VE LOOKED
AT THE LINKS YOU ATTACHED BUT CAN'T FIND ANYTHING THAT TIES IN WITH WHAT
I'M TRYING TO ACHIEVE

 

[Bob Phillips]

You want

=SUMPRODUCT(--(L2:L123=SUCCESSFUL),--(P2123=2007))

or

=SUMPRODUCT(--(L2:L123="SUCCESSFUL"),--(P2123=2007))

depending on whether SUCCESSFUL is a cell or text

 

[Shane Devenshire]

Hi,

First, your original formula
=COUNTIFS(L2:L123,D2123,SUCCESSFUL,P2123,2007)
does not do what you state. L2:L123 is the first criteria range and then
you are setting the criteria for that to D2123??? Then the second critera
range is SUCCESSFUL (must be a range name to be entered this way) and the
second condition is P2123, and so on. Using the L2:L123 range suggests you
are treating COUNTIFS like SUMIFS and they are not structured the same.

Let's suppose you are trying to count the number of items that contain the
word "Successful" in the range D2123 and the number 2007 in column P2123
2007:
=COUNTIFS(D2123,"SUCCESSFUL",P2123,2007)
2003:
=SUMPRODUCT(--(D2123="Successful"),--(P2123=2007))

Note that these formulas will fail if the range P2123 are dates and you
are trying to test for the year 2007. In that case you should replace
P2123 in the last formula with --(YEAR(P2123)=2007)

 

[yorkshirelass]

This won't work either - basically within the range D2123 are a range
of various outcomes of applications so there is either Awaiting
Decision, Successful and Unsuccessful - no other option. Within the
range P2123 is either the year either 2007 or 2008. Within the range
L2:L123 is the monetary value of the application. I want to count how
many applications have the following returns: Successful and 2007. I
will then be adapting this formula within a different cell to identify
Successful and 2008. In addition to this, I will will be running a
formula to display (in a different cell) the total amount of money for
successful applications within 2007 and 2008.

This worked within Excel 2007 using:
COUNTIFS(L2:L123,D2123,SUCCESSFUL,P2123,2007) and SUMIFS for the
monetary worth.

Does this give more information to what I need. I really appreciate
your help.

Thanks
Liz

 

[Bob Phillips]

Then I think Bernard gave you what you want, even though you used COUNTIFS
when you meant SUMIFS, and your COUNTIFS doesn't work. Other than that, I am
lost.

 

[yorkshirelass]

This worked - thank you very much. My next problem is trying to sum all
the monetary values within range L2:L123 if the criteria below is
correct i.e. that D2123=successful and P2-P123=2007. This is where I
used the SUMIFS formula within Excel 2007

Liz

 

[Bernd P]

Hello,

I suggest to use my UDF Pstat for this:
http://www.sulprobil.com/html/pstat.html

Regards,
Bernd