Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count multiple criteria
I'm trying to count how many times a combination of 2 criteria occur at the
same time across a range that sits in another worksheet in the same workbook so I can summarise the amount of sales (not value) by month & year. I've spent ages looking at the advice on this so far and tried various suggestions involving the sumproduct, countif and IF functions. I can manage the countif and sumif when there is just 1 criteria but when I add in the second one, I get 0 returned each time or with the sum and countif together, it adds both countifs up. Here's what I'm trying to do if anyone can help. Col A contains month and year and is custom formatted as mmm-yy eg Jan-08 Col B contains a numerical value eg £100 Col C - I've set up as a count column so it contains 1 all the way down. I'd like to know how many occurances of say Jan-08 in Col A also have a value (any number 1) showing in Col B. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count multiple criteria
You can only use SUMIF and COUNTIF when you have only one condition.
For more conditions you need to use SUMPRODUCT (and you can use this for summing and counting). Try this: =SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B1000)) Adjust the ranges to suit, but you can't use full-column references unless you have XL2007. Hope this helps. Pete On Dec 2, 3:44*pm, KJ wrote: I'm trying to count how many times a combination of 2 criteria occur at the same time across a range that sits in another worksheet in the same workbook so I can summarise the amount of sales (not value) by month & year. * I've spent ages looking at the advice on this so far and tried various suggestions involving the sumproduct, countif and IF functions. *I can manage the countif and sumif when there is just 1 criteria but when I add in the second one, I get 0 returned each time or with the sum and countif together, it adds both countifs up. Here's what I'm trying to do if anyone can help. Col A contains month and year and is custom formatted as mmm-yy eg Jan-08 Col B contains a numerical value eg £100 Col C - I've set up as a count column so it contains 1 all the way down. I'd like to know how many occurances of say Jan-08 in Col A also have a value (any number 1) showing in Col B. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count multiple criteria
Hi,
If you are using 2007 COUNTIFS(A1:A100,D1,B1:B100,"1") where you enter the date you want to check for in D1, any format. In 2003 =SUMPRODUCT(--(A1:A100=D1),--(B1:B1001)) again the date you want to check for is in D1 as any legitimate Excel date. If this helps, please click the Yes button Cheers, Shane Devenshire "KJ" wrote: I'm trying to count how many times a combination of 2 criteria occur at the same time across a range that sits in another worksheet in the same workbook so I can summarise the amount of sales (not value) by month & year. I've spent ages looking at the advice on this so far and tried various suggestions involving the sumproduct, countif and IF functions. I can manage the countif and sumif when there is just 1 criteria but when I add in the second one, I get 0 returned each time or with the sum and countif together, it adds both countifs up. Here's what I'm trying to do if anyone can help. Col A contains month and year and is custom formatted as mmm-yy eg Jan-08 Col B contains a numerical value eg £100 Col C - I've set up as a count column so it contains 1 all the way down. I'd like to know how many occurances of say Jan-08 in Col A also have a value (any number 1) showing in Col B. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count multiple criteria
Shane,
the OP indicated that s/he wanted counts for a particular month, not a specific date. Pete On Dec 2, 5:09*pm, Shane Devenshire wrote: Hi, If you are using 2007 COUNTIFS(A1:A100,D1,B1:B100,"1") where you enter the date you want to check for in D1, any format. In 2003 =SUMPRODUCT(--(A1:A100=D1),--(B1:B1001)) again the date you want to check for is in D1 as any legitimate Excel date. If this helps, please click the Yes button Cheers, Shane Devenshire "KJ" wrote: I'm trying to count how many times a combination of 2 criteria occur at the same time across a range that sits in another worksheet in the same workbook so I can summarise the amount of sales (not value) by month & year. * I've spent ages looking at the advice on this so far and tried various suggestions involving the sumproduct, countif and IF functions. *I can manage the countif and sumif when there is just 1 criteria but when I add in the second one, I get 0 returned each time or with the sum and countif together, it adds both countifs up. Here's what I'm trying to do if anyone can help. Col A contains month and year and is custom formatted as mmm-yy eg Jan-08 Col B contains a numerical value eg £100 Col C - I've set up as a count column so it contains 1 all the way down.. I'd like to know how many occurances of say Jan-08 in Col A also have a value (any number 1) showing in Col B. Thanks.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count multiple criteria
Thanks for that. Seems to work a treat on my sample. Will have a go on the
real thing tomorrow. Didn't have the TEXT bit or mmm-yy at all - simply went into range, "Jan-08". Also, hadn't realised I couldn't use full column references in v2003. Thank you kindly. "Pete_UK" wrote: You can only use SUMIF and COUNTIF when you have only one condition. For more conditions you need to use SUMPRODUCT (and you can use this for summing and counting). Try this: =SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B1000)) Adjust the ranges to suit, but you can't use full-column references unless you have XL2007. Hope this helps. Pete On Dec 2, 3:44 pm, KJ wrote: I'm trying to count how many times a combination of 2 criteria occur at the same time across a range that sits in another worksheet in the same workbook so I can summarise the amount of sales (not value) by month & year. I've spent ages looking at the advice on this so far and tried various suggestions involving the sumproduct, countif and IF functions. I can manage the countif and sumif when there is just 1 criteria but when I add in the second one, I get 0 returned each time or with the sum and countif together, it adds both countifs up. Here's what I'm trying to do if anyone can help. Col A contains month and year and is custom formatted as mmm-yy eg Jan-08 Col B contains a numerical value eg £100 Col C - I've set up as a count column so it contains 1 all the way down. I'd like to know how many occurances of say Jan-08 in Col A also have a value (any number 1) showing in Col B. Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count multiple criteria
Thanks for the reply. I'm using v2003. Can you confirm what the -- is. Is
this the sheet ref or something else (excuse my ignorance). Also, does the date I put in need to be in " "? "Shane Devenshire" wrote: Hi, If you are using 2007 COUNTIFS(A1:A100,D1,B1:B100,"1") where you enter the date you want to check for in D1, any format. In 2003 =SUMPRODUCT(--(A1:A100=D1),--(B1:B1001)) again the date you want to check for is in D1 as any legitimate Excel date. If this helps, please click the Yes button Cheers, Shane Devenshire "KJ" wrote: I'm trying to count how many times a combination of 2 criteria occur at the same time across a range that sits in another worksheet in the same workbook so I can summarise the amount of sales (not value) by month & year. I've spent ages looking at the advice on this so far and tried various suggestions involving the sumproduct, countif and IF functions. I can manage the countif and sumif when there is just 1 criteria but when I add in the second one, I get 0 returned each time or with the sum and countif together, it adds both countifs up. Here's what I'm trying to do if anyone can help. Col A contains month and year and is custom formatted as mmm-yy eg Jan-08 Col B contains a numerical value eg £100 Col C - I've set up as a count column so it contains 1 all the way down. I'd like to know how many occurances of say Jan-08 in Col A also have a value (any number 1) showing in Col B. Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count multiple criteria
You're welcome - thanks for feeding back.
Pete On Dec 2, 11:03*pm, KJ wrote: Thanks for that. *Seems to work a treat on my sample. *Will have a go on the real thing tomorrow. *Didn't have the TEXT bit or mmm-yy at all - simply went into range, "Jan-08". *Also, hadn't realised I couldn't use full column references in v2003. Thank you kindly. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count multiple criteria
Hi Pete,
I've now applied your function to my master sheet and it works great. However, I've met two further problems. 1. By using the 0, it then doesn't include any 0 entries I might have in Col B (Sales Value). I changed the formula to =0 at the end but it then included the cells that were blank too (cells may be blank until I've agreed a sale price). =SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B100=0)) Is there a way of setting the 2nd criteria to be a value or entry of any kind eg wildcard*. 2. I also need to calculate the value of all the sales when both my criteria are met. eg. sum if Col A is Jan-08 and Col B has an entry of any value, what is the sum of the values in Col B. Have tried amending the sumproduct function but can't quite get there. Thanks for any help you can offer. "Pete_UK" wrote: You're welcome - thanks for feeding back. Pete On Dec 2, 11:03 pm, KJ wrote: Thanks for that. Seems to work a treat on my sample. Will have a go on the real thing tomorrow. Didn't have the TEXT bit or mmm-yy at all - simply went into range, "Jan-08". Also, hadn't realised I couldn't use full column references in v2003. Thank you kindly. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Using Multiple Criteria | Excel Worksheet Functions | |||
Count multiple cells against multiple criteria in an Excel spreads | Excel Worksheet Functions | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
COUNT WITH MULTIPLE CRITERIA | Excel Worksheet Functions | |||
I need to count while using multiple criteria | Excel Worksheet Functions |