Re: COUNTIF function for multiple criteria

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


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" <yorkshirelass.3tvicw@xxxxxxxxxxxxxxx> wrote in
message
news:yorkshirelass.3tvicw@xxxxxxxxxxxxxxxxxx

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 :confused:


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" <yorkshirelass.3tvd91@xxxxxxxxxxxxxxx> wrote in
message
news:yorkshirelass.3tvd91@xxxxxxxxxxxxxxxxxx

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/member.php?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/showthread.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/showthread.php?t=107028)



--
yorkshirelass
------------------------------------------------------------------------
yorkshirelass's Profile: http://www.thecodecage.com/forumz/member.php?userid=429
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=107028

.

Relevant Pages

  • Job: Lamp Developer (NYC based media company)
    ... Experienced LAMP Applications Developer ... resume, links to successful projects, and a code sample (100 or more ... development-engineering environment, assisting in the architectural ...
    (comp.lang.php)
  • Re: Telephone security - is there a competent UK bank?
    ... >>million people are blacklisted, which is complete bollocks. ... "There will also be a record of all your past and present applications ... for loans and credit..." ... were successful - if the credit a/c subsequently appears on the file then ...
    (uk.finance)
  • Re: Re: C# programmer wants to learn assembly?? plz help
    ... are the Linux Applications written with NASM? ... Where are all these FASM applications, if FASM is so successful? ... language isn't exactly the proof that an assembler is popular or not. ...
    (alt.lang.asm)
  • Re: .NET 3.0 Final
    ... better applications quicker and for less cost your clients will start asking what is going on. ... So far I haven't seen any definitive study saying that developing in Delphi for .Net instead of Delphi for Win32 produces better applications more quickly and costs the consumer less money. ... Now, I've modified that question to be - "Name one, successful, commercial application that is written exclusively using .Net." ...
    (borland.public.delphi.non-technical)
  • Re: COUNTIF function for multiple criteria
    ... Bernard Liengme;382702 Wrote: ... if the word Successful is text and not a Named cell ... 'The Code Cage Forums - View Profile: yorkshirelass' ...
    (microsoft.public.excel.worksheet.functions)