Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting with multiple criteria in separate Columns
Hello,
I'm trying to do a formula to count records (rows) matching multiple different criteria's (in different columns) where all conditions must be true. I tried to do nested COUNTIF or SUMIF and combination of COUNTIF + ADD or IF conditions, The spreadsheet accepted the formula; however, not producing the results expected. For example, spreadsheet A is list of all the incoming calls & their particulars for each day of the month. And Col A has the date of the call, and Col E has the priority of the request. Row Col = A(date) B(record #) C (Caller) D (product) E (priority) 1 04/01/2009 0001 John Smith desk H 2 04/01/2009 0002 Jane Doe Table 3 04/02/2009 0003 Bob Smith chair H 4 04/04/2009 0004 Tom Molly window H 5 04/04/2009 0005 Mary Doe desk H 6 04/04/2009 0006 Frank Doe chair So, I tried to get the daily summary be putting calculation into Spreadsheet B with a row represent each day of the months down Col A. And, this is what I expect to get Row/Col = A(date) B (# of request) C (# H priority) D(request for Desk),..... 1 04/01/2009 2 1 1 2 04/02/2009 1 0 0 3 04/03/2009 0 0 0 4 04/04/2009 3 2 1 5 04/05/2009 6 04/06/2009 So, got the daily call total with a simple COUNTIF($A$1:$A$6, A1) and copied that down the column A Next want to get the daily total calls with H priority (as H in Col E in Spreadsheet A), and the total request for each product. And tried several combinations of COUNTIF, SUMIF, with nested AND or IF conditions. But can't get it working. What can you suggest. Thanks ! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting with multiple criteria in separate Columns
Check out this link...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html =sumproduct(--($A$1:$A$6=A1), --($E$1:$E$6 = "H")) or something like that... -- HTH... Jim Thomlinson "Felicia" wrote: Hello, I'm trying to do a formula to count records (rows) matching multiple different criteria's (in different columns) where all conditions must be true. I tried to do nested COUNTIF or SUMIF and combination of COUNTIF + ADD or IF conditions, The spreadsheet accepted the formula; however, not producing the results expected. For example, spreadsheet A is list of all the incoming calls & their particulars for each day of the month. And Col A has the date of the call, and Col E has the priority of the request. Row Col = A(date) B(record #) C (Caller) D (product) E (priority) 1 04/01/2009 0001 John Smith desk H 2 04/01/2009 0002 Jane Doe Table 3 04/02/2009 0003 Bob Smith chair H 4 04/04/2009 0004 Tom Molly window H 5 04/04/2009 0005 Mary Doe desk H 6 04/04/2009 0006 Frank Doe chair So, I tried to get the daily summary be putting calculation into Spreadsheet B with a row represent each day of the months down Col A. And, this is what I expect to get Row/Col = A(date) B (# of request) C (# H priority) D(request for Desk),..... 1 04/01/2009 2 1 1 2 04/02/2009 1 0 0 3 04/03/2009 0 0 0 4 04/04/2009 3 2 1 5 04/05/2009 6 04/06/2009 So, got the daily call total with a simple COUNTIF($A$1:$A$6, A1) and copied that down the column A Next want to get the daily total calls with H priority (as H in Col E in Spreadsheet A), and the total request for each product. And tried several combinations of COUNTIF, SUMIF, with nested AND or IF conditions. But can't get it working. What can you suggest. Thanks ! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting with multiple criteria in separate Columns
Thanks Jim: It works great ! Took me some experimenting, but finally got it working with this formate: =sumproduct((TableA!($A$1:$A$6=A1)*(TableA($E$1:$E $6 = "H")). The article on the website you referred to is very helpful; have to read thru the whole thing at some point still. This take me to the next step and seem now to have problem with trying to match certain key word in the target cell by using wildcard charter. So, I have to do the same process with 2 different source of data. In first case, got it working with the above formula where the 2nd criteria is comparing to a fixed value. However, with the 2nd data source, I need to find records that contain a certain key word from a longer string of data in the cell. So, same example of table as before, but Col E now contains free from text description of the request. And it may start with the word RUSH if it is urgent. And that word could appear in variety of formate such as (RUSH, Rush, rush, **** Rush ***, and so on). So I coded the formula as : =sumproduct((TableA!($A$1:$A$6=A1)*(TableA($E$1:$E $6 = "*Rush")). Yet it won't pick up the records. Strange thing is that I tried doing a countif with the same wildcard expression and it works. =COUNTIF(TableA!($E1$1:$E$6,"*Rush*") And it can find those records. So, wonder if there is something about using '*' as wildcard charter with SUMPRODUCT ? And how do I get around this ? Thanks, Felicia "Jim Thomlinson" wrote: Check out this link... http://www.xldynamic.com/source/xld.SUMPRODUCT.html =sumproduct(--($A$1:$A$6=A1), --($E$1:$E$6 = "H")) or something like that... -- HTH... Jim Thomlinson "Felicia" wrote: Hello, I'm trying to do a formula to count records (rows) matching multiple different criteria's (in different columns) where all conditions must be true. I tried to do nested COUNTIF or SUMIF and combination of COUNTIF + ADD or IF conditions, The spreadsheet accepted the formula; however, not producing the results expected. For example, spreadsheet A is list of all the incoming calls & their particulars for each day of the month. And Col A has the date of the call, and Col E has the priority of the request. Row Col = A(date) B(record #) C (Caller) D (product) E (priority) 1 04/01/2009 0001 John Smith desk H 2 04/01/2009 0002 Jane Doe Table 3 04/02/2009 0003 Bob Smith chair H 4 04/04/2009 0004 Tom Molly window H 5 04/04/2009 0005 Mary Doe desk H 6 04/04/2009 0006 Frank Doe chair So, I tried to get the daily summary be putting calculation into Spreadsheet B with a row represent each day of the months down Col A. And, this is what I expect to get Row/Col = A(date) B (# of request) C (# H priority) D(request for Desk),..... 1 04/01/2009 2 1 1 2 04/02/2009 1 0 0 3 04/03/2009 0 0 0 4 04/04/2009 3 2 1 5 04/05/2009 6 04/06/2009 So, got the daily call total with a simple COUNTIF($A$1:$A$6, A1) and copied that down the column A Next want to get the daily total calls with H priority (as H in Col E in Spreadsheet A), and the total request for each product. And tried several combinations of COUNTIF, SUMIF, with nested AND or IF conditions. But can't get it working. What can you suggest. Thanks ! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting with multiple criteria in separate Columns
Hi Jim:
What's wrong with this formula? =SUMPRODUCT(--(A1:A2000="*:*"), --(M1:M2000"89.9")) I get "0" but that's not the correct answer for the formula. Any ideas? Thanks, Greg "Felicia" wrote: Thanks Jim: It works great ! Took me some experimenting, but finally got it working with this formate: =sumproduct((TableA!($A$1:$A$6=A1)*(TableA($E$1:$E $6 = "H")). The article on the website you referred to is very helpful; have to read thru the whole thing at some point still. This take me to the next step and seem now to have problem with trying to match certain key word in the target cell by using wildcard charter. So, I have to do the same process with 2 different source of data. In first case, got it working with the above formula where the 2nd criteria is comparing to a fixed value. However, with the 2nd data source, I need to find records that contain a certain key word from a longer string of data in the cell. So, same example of table as before, but Col E now contains free from text description of the request. And it may start with the word RUSH if it is urgent. And that word could appear in variety of formate such as (RUSH, Rush, rush, **** Rush ***, and so on). So I coded the formula as : =sumproduct((TableA!($A$1:$A$6=A1)*(TableA($E$1:$E $6 = "*Rush")). Yet it won't pick up the records. Strange thing is that I tried doing a countif with the same wildcard expression and it works. =COUNTIF(TableA!($E1$1:$E$6,"*Rush*") And it can find those records. So, wonder if there is something about using '*' as wildcard charter with SUMPRODUCT ? And how do I get around this ? Thanks, Felicia "Jim Thomlinson" wrote: Check out this link... http://www.xldynamic.com/source/xld.SUMPRODUCT.html =sumproduct(--($A$1:$A$6=A1), --($E$1:$E$6 = "H")) or something like that... -- HTH... Jim Thomlinson "Felicia" wrote: Hello, I'm trying to do a formula to count records (rows) matching multiple different criteria's (in different columns) where all conditions must be true. I tried to do nested COUNTIF or SUMIF and combination of COUNTIF + ADD or IF conditions, The spreadsheet accepted the formula; however, not producing the results expected. For example, spreadsheet A is list of all the incoming calls & their particulars for each day of the month. And Col A has the date of the call, and Col E has the priority of the request. Row Col = A(date) B(record #) C (Caller) D (product) E (priority) 1 04/01/2009 0001 John Smith desk H 2 04/01/2009 0002 Jane Doe Table 3 04/02/2009 0003 Bob Smith chair H 4 04/04/2009 0004 Tom Molly window H 5 04/04/2009 0005 Mary Doe desk H 6 04/04/2009 0006 Frank Doe chair So, I tried to get the daily summary be putting calculation into Spreadsheet B with a row represent each day of the months down Col A. And, this is what I expect to get Row/Col = A(date) B (# of request) C (# H priority) D(request for Desk),..... 1 04/01/2009 2 1 1 2 04/02/2009 1 0 0 3 04/03/2009 0 0 0 4 04/04/2009 3 2 1 5 04/05/2009 6 04/06/2009 So, got the daily call total with a simple COUNTIF($A$1:$A$6, A1) and copied that down the column A Next want to get the daily total calls with H priority (as H in Col E in Spreadsheet A), and the total request for each product. And tried several combinations of COUNTIF, SUMIF, with nested AND or IF conditions. But can't get it working. What can you suggest. Thanks ! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting with multiple criteria in separate Columns
See a response to your previous post, but remember to add the stuff in column M.
Greg wrote: Hi Jim: What's wrong with this formula? =SUMPRODUCT(--(A1:A2000="*:*"), --(M1:M2000"89.9")) I get "0" but that's not the correct answer for the formula. Any ideas? Thanks, Greg "Felicia" wrote: Thanks Jim: It works great ! Took me some experimenting, but finally got it working with this formate: =sumproduct((TableA!($A$1:$A$6=A1)*(TableA($E$1:$E $6 = "H")). The article on the website you referred to is very helpful; have to read thru the whole thing at some point still. This take me to the next step and seem now to have problem with trying to match certain key word in the target cell by using wildcard charter. So, I have to do the same process with 2 different source of data. In first case, got it working with the above formula where the 2nd criteria is comparing to a fixed value. However, with the 2nd data source, I need to find records that contain a certain key word from a longer string of data in the cell. So, same example of table as before, but Col E now contains free from text description of the request. And it may start with the word RUSH if it is urgent. And that word could appear in variety of formate such as (RUSH, Rush, rush, **** Rush ***, and so on). So I coded the formula as : =sumproduct((TableA!($A$1:$A$6=A1)*(TableA($E$1:$E $6 = "*Rush")). Yet it won't pick up the records. Strange thing is that I tried doing a countif with the same wildcard expression and it works. =COUNTIF(TableA!($E1$1:$E$6,"*Rush*") And it can find those records. So, wonder if there is something about using '*' as wildcard charter with SUMPRODUCT ? And how do I get around this ? Thanks, Felicia "Jim Thomlinson" wrote: Check out this link... http://www.xldynamic.com/source/xld.SUMPRODUCT.html =sumproduct(--($A$1:$A$6=A1), --($E$1:$E$6 = "H")) or something like that... -- HTH... Jim Thomlinson "Felicia" wrote: Hello, I'm trying to do a formula to count records (rows) matching multiple different criteria's (in different columns) where all conditions must be true. I tried to do nested COUNTIF or SUMIF and combination of COUNTIF + ADD or IF conditions, The spreadsheet accepted the formula; however, not producing the results expected. For example, spreadsheet A is list of all the incoming calls & their particulars for each day of the month. And Col A has the date of the call, and Col E has the priority of the request. Row Col = A(date) B(record #) C (Caller) D (product) E (priority) 1 04/01/2009 0001 John Smith desk H 2 04/01/2009 0002 Jane Doe Table 3 04/02/2009 0003 Bob Smith chair H 4 04/04/2009 0004 Tom Molly window H 5 04/04/2009 0005 Mary Doe desk H 6 04/04/2009 0006 Frank Doe chair So, I tried to get the daily summary be putting calculation into Spreadsheet B with a row represent each day of the months down Col A. And, this is what I expect to get Row/Col = A(date) B (# of request) C (# H priority) D(request for Desk),..... 1 04/01/2009 2 1 1 2 04/02/2009 1 0 0 3 04/03/2009 0 0 0 4 04/04/2009 3 2 1 5 04/05/2009 6 04/06/2009 So, got the daily call total with a simple COUNTIF($A$1:$A$6, A1) and copied that down the column A Next want to get the daily total calls with H priority (as H in Col E in Spreadsheet A), and the total request for each product. And tried several combinations of COUNTIF, SUMIF, with nested AND or IF conditions. But can't get it working. What can you suggest. Thanks ! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting multiple criteria across columns and rows??? | Excel Worksheet Functions | |||
Counting multiple criteria across columns and rows??? | Excel Worksheet Functions | |||
sumproduct using criteria from multiple columns in a separate wksh | Excel Worksheet Functions | |||
Counting Multiple columns using single criteria | Excel Worksheet Functions | |||
Help w/ counting multiple columns based on IF criteria | Excel Worksheet Functions |