|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 16th, 2009, 11:37 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF function for multiple criteria
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  123,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 
|
|
#2
June 16th, 2009, 12:04 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF function for multiple criteria
=SUMPRODUCT(--(D2
123=SUCCESSFUL),--(P2:P123=2007),L2:L123)or, if the word Successful is text and not a Named cell =SUMPRODUCT(--(D2 123="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 123,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
|
|
#3
June 16th, 2009, 01:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF function for multiple criteria
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 123=SUCCESSFUL),--(P2:P123=2007),L2:L123)or, if the word Successful is text and not a Named cell =SUMPRODUCT(--(D2 123="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 \"--\"' (http://mcgimpsey.com/excel/formulae/doubleneg.html) best wishes -- Bernard V Liengme Microsoft Excel MVP '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 123,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' (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
|
|
#4
June 16th, 2009, 03:49 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF function for multiple criteria
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 123=SUCCESSFUL),--(P2:P123=2007),L2:L123)or, if the word Successful is text and not a Named cell =SUMPRODUCT(--(D2 123="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 \"--\"' (http://mcgimpsey.com/excel/formulae/doubleneg.html) best wishes -- Bernard V Liengme Microsoft Excel MVP '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 123,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' (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
|
|
#5
June 16th, 2009, 04:09 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF function for multiple criteria
Hi,
First, your original formula =COUNTIFS(L2:L123,D2 123,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 123??? 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 123 and the number 2007 in column P2:P1232007: =COUNTIFS(D2 123,"SUCCESSFUL",P2:P123,2007)2003: =SUMPRODUCT(--(D2 123="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 123=SUCCESSFUL),--(P2:P123=2007),L2:L123)or, if the word Successful is text and not a Named cell =SUMPRODUCT(--(D2 123="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 \"--\"' (http://mcgimpsey.com/excel/formulae/doubleneg.html) best wishes -- Bernard V Liengme Microsoft Excel MVP '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 123,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' (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
|
|
#6
June 16th, 2009, 04:21 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF function for multiple criteria
This won't work either - basically within the range D2 123 are a rangeof 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 123,SUCCESSFUL,P2:P123,2007) and SUMIFS for themonetary 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 123=SUCCESSFUL),--(P2:P123=2007),L2:L123)or, if the word Successful is text and not a Named cell =SUMPRODUCT(--(D2 123="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 123,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
|
|
#7
June 16th, 2009, 06:29 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF function for multiple criteria
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. -- __________________________________ HTH Bob "yorkshirelass" wrote in message ... This won't work either - basically within the range D2 123 are a rangeof 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 123,SUCCESSFUL,P2:P123,2007) and SUMIFS for themonetary 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 123=SUCCESSFUL),--(P2:P123=2007),L2:L123)or, if the word Successful is text and not a Named cell =SUMPRODUCT(--(D2 123="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 123,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
|
|
#8
June 18th, 2009, 11:34 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF function for multiple criteria
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 123=successful and P2-P123=2007. This is where Iused the SUMIFS formula within Excel 2007 Liz Shane Devenshire;383055 Wrote: Hi, First, your original formula =COUNTIFS(L2:L123,D2 123,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 123??? Then the secondcritera 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 123 and the number 2007 in columnP2:P123 2007: =COUNTIFS(D2 123,"SUCCESSFUL",P2:P123,2007)2003: =SUMPRODUCT(--(D2 123="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 123=SUCCESSFUL),--(P2:P123=2007),L2:L123)or, if the word Successful is text and not a Named cell =SUMPRODUCT(--(D2 123="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 123,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
|
|
#9
June 18th, 2009, 01:45 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF function for multiple criteria
Hello,
I suggest to use my UDF Pstat for this: http://www.sulprobil.com/html/pstat.html Regards, Bernd
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
