=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
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
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:
http://www.thecodecage.com/forumz/member.php?userid=429
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=107028