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 D2:D123=successful and P2-P123=2007. This is where I
used the SUMIFS formula within Excel 2007
Liz
Shane Devenshire;383055 Wrote:
Hi,
First, your original formula
=COUNTIFS(L2:L123,D2:D123,SUCCESSFUL,P2:P123,2007)
does not do what you state. L2:L123 is the first criteria range and
then
you are setting the criteria for that to D2:D123??? Then the second
critera
range is SUCCESSFUL (must be a range name to be entered this way) and
the
second condition is P2:P123, 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 D2:D123 and the number 2007 in column
P2:P123
2007:
=COUNTIFS(D2:D123,"SUCCESSFUL",P2:P123,2007)
2003:
=SUMPRODUCT(--(D2:D123="Successful"),--(P2:P123=2007))
Note that these formulas will fail if the range P2:P123 are dates and
you
are trying to test for the year 2007. In that case you should replace
P2:P123 in the last formula with --(YEAR(P2:P123)=2007)
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
"yorkshirelass" wrote:
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 
Bernard Liengme;382702 Wrote:
=SUMPRODUCT(--(D2:D123=SUCCESSFUL),--(P2:P123=2007),L2:L123)
or, if the word Successful is text and not a Named cell
=SUMPRODUCT(--(D2:D123="SUCCESSFUL"),--(P2:P123=2007),L2:L123)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
'McGimpsey & Associates : Excel : Formulae : Why \"--\"'
('McGimpsey & Associates : Excel : Formulae : Why \"--\"'
(http://mcgimpsey.com/excel/formulae/doubleneg.html))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
'Bernard Liengme' ('Bernard Liengme'
(http://people.stfx.ca/bliengme))
remove caps from email
"yorkshirelass" wrote in
message
...
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,D2:D123,SUCCESSFUL,P2:P123,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.
--
yorkshirelass
------------------------------------------------------------------------
yorkshirelass's Profile:
'The Code Cage Forums - View Profile: yorkshirelass'
('The Code Cage Forums - View Profile: yorkshirelass'
(http://www.thecodecage.com/forumz/me...hp?userid=429))
View this thread:
'COUNTIF function for multiple criteria - The Code Cage Forums'
('COUNTIF function for multiple criteria - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh....php?t=107028))
--
yorkshirelass
------------------------------------------------------------------------
yorkshirelass's Profile: 'The Code Cage Forums - View Profile:
yorkshirelass'
(http://www.thecodecage.com/forumz/member.php?userid=429)
View this thread: 'COUNTIF function for multiple criteria - The Code
Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=107028)
--
yorkshirelass
------------------------------------------------------------------------
yorkshirelass's Profile:
http://www.thecodecage.com/forumz/member.php?userid=429
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=107028