This won't work either - basically within the range D2:D123 are a range
of various outcomes of applications so there is either Awaiting
Decision, Successful and Unsuccessful - no other option. Within the
range P2:P123 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,D2:D123,SUCCESSFUL,P2:P123,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;383017 Wrote:
You want
=SUMPRODUCT(--(L2:L123=SUCCESSFUL),--(P2:P123=2007))
or
=SUMPRODUCT(--(L2:L123="SUCCESSFUL"),--(P2:P123=2007))
depending on whether SUCCESSFUL is a cell or text
--
__________________________________
HTH
Bob
"yorkshirelass" wrote in
message
...
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