| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count on multiple conditions
I'm trying to perform a count based on two columns and two conditions. I've
searched the forum and found several options but can't get any of them to work. Will "countif" not work with date? Here's what I'm doing: A B neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 I want to use a code that will search column b for a date: 8/1/2006 and return a count of how many positives, negatives and neutrals I have within that date. Can anyone help? Thanks, Debbie |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count on multiple conditions
Try the function DCOUNT
Something like this: A B NAME DATE neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 D E NAME DATE neutral 8/1/2006 DCOUNT(A1:B4;2;D1:E2) Rodrigo Ferreira "Debbie" escreveu na mensagem ... I'm trying to perform a count based on two columns and two conditions. I've searched the forum and found several options but can't get any of them to work. Will "countif" not work with date? Here's what I'm doing: A B neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 I want to use a code that will search column b for a date: 8/1/2006 and return a count of how many positives, negatives and neutrals I have within that date. Can anyone help? Thanks, Debbie |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count on multiple conditions
Thank you but this didn't fix my problem.
I'm sure I wasn't clear enough. A B neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 Negative 8/5/2006 Positive 8/7/2006 Positive 8/7/2006 neutral 8/7/2006 positive neutral negative 1-Aug 0 DCOUNT(A1:B4;2;D1:E2) 0 2-Aug 0 0 1 3-Aug 0 0 1 4-Aug 0 1 2 5-Aug 0 1 3 6-Aug 0 0 3 7-Aug 1 0 8 You see, I want it to count how many positives I have for 8/1 and return that number. I want it to count how many neutrals I have for 8/1 and return that number. Same thing for negatives. I need it to perform this action through out an entire section of information and decipher how many per date and enter that number in the appropriate column. So in the 8/1 section under positive I should have a 1, under the neutral I should have 1 and in the negative, I should have a 0. I have no information for 8/2, 8/3 and 8/4 so those should all be 0's. Then for 8/5 I should have 0 positives, 0 neutrals and 2 negatives. The formula you gave me doesn't fit this scenario. Any other ideas? Thanks for your help, Debbie "Rodrigo Ferreira" wrote: Try the function DCOUNT Something like this: A B NAME DATE neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 D E NAME DATE neutral 8/1/2006 DCOUNT(A1:B4;2;D1:E2) Rodrigo Ferreira "Debbie" escreveu na mensagem ... I'm trying to perform a count based on two columns and two conditions. I've searched the forum and found several options but can't get any of them to work. Will "countif" not work with date? Here's what I'm doing: A B neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 I want to use a code that will search column b for a date: 8/1/2006 and return a count of how many positives, negatives and neutrals I have within that date. Can anyone help? Thanks, Debbie |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count on multiple conditions
How about a pivot table... Place your cursor in the middle of your data and
then select Data - Pivot Table. Now just follow the wizard (or just select Finish as the defaults are normally correct). Place your Dates in the left column and your Values(Positive / negative / neutral) across the top. You also want to place your values in the middle. This will give you your counts. If you need to you could also get fancy and group your dates to consolidate by month, quarter or year if that was of interest to you... Reply back if this does not make sense... -- HTH... Jim Thomlinson "Debbie" wrote: Thank you but this didn't fix my problem. I'm sure I wasn't clear enough. A B neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 Negative 8/5/2006 Positive 8/7/2006 Positive 8/7/2006 neutral 8/7/2006 positive neutral negative 1-Aug 0 DCOUNT(A1:B4;2;D1:E2) 0 2-Aug 0 0 1 3-Aug 0 0 1 4-Aug 0 1 2 5-Aug 0 1 3 6-Aug 0 0 3 7-Aug 1 0 8 You see, I want it to count how many positives I have for 8/1 and return that number. I want it to count how many neutrals I have for 8/1 and return that number. Same thing for negatives. I need it to perform this action through out an entire section of information and decipher how many per date and enter that number in the appropriate column. So in the 8/1 section under positive I should have a 1, under the neutral I should have 1 and in the negative, I should have a 0. I have no information for 8/2, 8/3 and 8/4 so those should all be 0's. Then for 8/5 I should have 0 positives, 0 neutrals and 2 negatives. The formula you gave me doesn't fit this scenario. Any other ideas? Thanks for your help, Debbie "Rodrigo Ferreira" wrote: Try the function DCOUNT Something like this: A B NAME DATE neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 D E NAME DATE neutral 8/1/2006 DCOUNT(A1:B4;2;D1:E2) Rodrigo Ferreira "Debbie" escreveu na mensagem ... I'm trying to perform a count based on two columns and two conditions. I've searched the forum and found several options but can't get any of them to work. Will "countif" not work with date? Here's what I'm doing: A B neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 I want to use a code that will search column b for a date: 8/1/2006 and return a count of how many positives, negatives and neutrals I have within that date. Can anyone help? Thanks, Debbie |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count on multiple conditions
Jim, that makes sense, but I don't see this working well for my needs on this
project. I have pivot tables set to retrieve the original information from my database. Now I need to transform this information into something more logical that my clients can understand. Unless I just don't understand pivot tables well enough, I think I'm looking at a lot more work than this should be to achieve a simple count of date vs pos/neg/neut. Thanks, Debbie "Jim Thomlinson" wrote: How about a pivot table... Place your cursor in the middle of your data and then select Data - Pivot Table. Now just follow the wizard (or just select Finish as the defaults are normally correct). Place your Dates in the left column and your Values(Positive / negative / neutral) across the top. You also want to place your values in the middle. This will give you your counts. If you need to you could also get fancy and group your dates to consolidate by month, quarter or year if that was of interest to you... Reply back if this does not make sense... -- HTH... Jim Thomlinson "Debbie" wrote: Thank you but this didn't fix my problem. I'm sure I wasn't clear enough. A B neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 Negative 8/5/2006 Positive 8/7/2006 Positive 8/7/2006 neutral 8/7/2006 positive neutral negative 1-Aug 0 DCOUNT(A1:B4;2;D1:E2) 0 2-Aug 0 0 1 3-Aug 0 0 1 4-Aug 0 1 2 5-Aug 0 1 3 6-Aug 0 0 3 7-Aug 1 0 8 You see, I want it to count how many positives I have for 8/1 and return that number. I want it to count how many neutrals I have for 8/1 and return that number. Same thing for negatives. I need it to perform this action through out an entire section of information and decipher how many per date and enter that number in the appropriate column. So in the 8/1 section under positive I should have a 1, under the neutral I should have 1 and in the negative, I should have a 0. I have no information for 8/2, 8/3 and 8/4 so those should all be 0's. Then for 8/5 I should have 0 positives, 0 neutrals and 2 negatives. The formula you gave me doesn't fit this scenario. Any other ideas? Thanks for your help, Debbie "Rodrigo Ferreira" wrote: Try the function DCOUNT Something like this: A B NAME DATE neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 D E NAME DATE neutral 8/1/2006 DCOUNT(A1:B4;2;D1:E2) Rodrigo Ferreira "Debbie" escreveu na mensagem ... I'm trying to perform a count based on two columns and two conditions. I've searched the forum and found several options but can't get any of them to work. Will "countif" not work with date? Here's what I'm doing: A B neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 I want to use a code that will search column b for a date: 8/1/2006 and return a count of how many positives, negatives and neutrals I have within that date. Can anyone help? Thanks, Debbie |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count on multiple conditions
Not too sure why you think it will not work ( I did it as a test on my end
and it came out just fine) but you could also use sumproduct if that better suits your needs. Creating a table similar to the result you are looking for Starting at D1 positive Negative neutral 8/1/2006 1 0 1 8/5/2006 0 2 0 8/7/2006 2 0 1 The formula in E2 would be =SUMPRODUCT(--($D2=$B$2:$B$8), --(E$1=$A$2:$A$8)) This formula can be dragged to suit... A pivot table can create the exact same thing though... -- HTH... Jim Thomlinson "Debbie" wrote: Jim, that makes sense, but I don't see this working well for my needs on this project. I have pivot tables set to retrieve the original information from my database. Now I need to transform this information into something more logical that my clients can understand. Unless I just don't understand pivot tables well enough, I think I'm looking at a lot more work than this should be to achieve a simple count of date vs pos/neg/neut. Thanks, Debbie "Jim Thomlinson" wrote: How about a pivot table... Place your cursor in the middle of your data and then select Data - Pivot Table. Now just follow the wizard (or just select Finish as the defaults are normally correct). Place your Dates in the left column and your Values(Positive / negative / neutral) across the top. You also want to place your values in the middle. This will give you your counts. If you need to you could also get fancy and group your dates to consolidate by month, quarter or year if that was of interest to you... Reply back if this does not make sense... -- HTH... Jim Thomlinson "Debbie" wrote: Thank you but this didn't fix my problem. I'm sure I wasn't clear enough. A B neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 Negative 8/5/2006 Positive 8/7/2006 Positive 8/7/2006 neutral 8/7/2006 positive neutral negative 1-Aug 0 DCOUNT(A1:B4;2;D1:E2) 0 2-Aug 0 0 1 3-Aug 0 0 1 4-Aug 0 1 2 5-Aug 0 1 3 6-Aug 0 0 3 7-Aug 1 0 8 You see, I want it to count how many positives I have for 8/1 and return that number. I want it to count how many neutrals I have for 8/1 and return that number. Same thing for negatives. I need it to perform this action through out an entire section of information and decipher how many per date and enter that number in the appropriate column. So in the 8/1 section under positive I should have a 1, under the neutral I should have 1 and in the negative, I should have a 0. I have no information for 8/2, 8/3 and 8/4 so those should all be 0's. Then for 8/5 I should have 0 positives, 0 neutrals and 2 negatives. The formula you gave me doesn't fit this scenario. Any other ideas? Thanks for your help, Debbie "Rodrigo Ferreira" wrote: Try the function DCOUNT Something like this: A B NAME DATE neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 D E NAME DATE neutral 8/1/2006 DCOUNT(A1:B4;2;D1:E2) Rodrigo Ferreira "Debbie" escreveu na mensagem ... I'm trying to perform a count based on two columns and two conditions. I've searched the forum and found several options but can't get any of them to work. Will "countif" not work with date? Here's what I'm doing: A B neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 I want to use a code that will search column b for a date: 8/1/2006 and return a count of how many positives, negatives and neutrals I have within that date. Can anyone help? Thanks, Debbie |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count on multiple conditions
Jim,
You're absolutely right. The pivot did exactly as it should. I'm apparently on another planet today. Thanks for your help. Debbie "Jim Thomlinson" wrote: Not too sure why you think it will not work ( I did it as a test on my end and it came out just fine) but you could also use sumproduct if that better suits your needs. Creating a table similar to the result you are looking for Starting at D1 positive Negative neutral 8/1/2006 1 0 1 8/5/2006 0 2 0 8/7/2006 2 0 1 The formula in E2 would be =SUMPRODUCT(--($D2=$B$2:$B$8), --(E$1=$A$2:$A$8)) This formula can be dragged to suit... A pivot table can create the exact same thing though... -- HTH... Jim Thomlinson "Debbie" wrote: Jim, that makes sense, but I don't see this working well for my needs on this project. I have pivot tables set to retrieve the original information from my database. Now I need to transform this information into something more logical that my clients can understand. Unless I just don't understand pivot tables well enough, I think I'm looking at a lot more work than this should be to achieve a simple count of date vs pos/neg/neut. Thanks, Debbie "Jim Thomlinson" wrote: How about a pivot table... Place your cursor in the middle of your data and then select Data - Pivot Table. Now just follow the wizard (or just select Finish as the defaults are normally correct). Place your Dates in the left column and your Values(Positive / negative / neutral) across the top. You also want to place your values in the middle. This will give you your counts. If you need to you could also get fancy and group your dates to consolidate by month, quarter or year if that was of interest to you... Reply back if this does not make sense... -- HTH... Jim Thomlinson "Debbie" wrote: Thank you but this didn't fix my problem. I'm sure I wasn't clear enough. A B neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 Negative 8/5/2006 Positive 8/7/2006 Positive 8/7/2006 neutral 8/7/2006 positive neutral negative 1-Aug 0 DCOUNT(A1:B4;2;D1:E2) 0 2-Aug 0 0 1 3-Aug 0 0 1 4-Aug 0 1 2 5-Aug 0 1 3 6-Aug 0 0 3 7-Aug 1 0 8 You see, I want it to count how many positives I have for 8/1 and return that number. I want it to count how many neutrals I have for 8/1 and return that number. Same thing for negatives. I need it to perform this action through out an entire section of information and decipher how many per date and enter that number in the appropriate column. So in the 8/1 section under positive I should have a 1, under the neutral I should have 1 and in the negative, I should have a 0. I have no information for 8/2, 8/3 and 8/4 so those should all be 0's. Then for 8/5 I should have 0 positives, 0 neutrals and 2 negatives. The formula you gave me doesn't fit this scenario. Any other ideas? Thanks for your help, Debbie "Rodrigo Ferreira" wrote: Try the function DCOUNT Something like this: A B NAME DATE neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 D E NAME DATE neutral 8/1/2006 DCOUNT(A1:B4;2;D1:E2) Rodrigo Ferreira "Debbie" escreveu na mensagem ... I'm trying to perform a count based on two columns and two conditions. I've searched the forum and found several options but can't get any of them to work. Will "countif" not work with date? Here's what I'm doing: A B neutral 8/1/2006 positive 8/1/2006 negative 8/5/2006 I want to use a code that will search column b for a date: 8/1/2006 and return a count of how many positives, negatives and neutrals I have within that date. Can anyone help? Thanks, Debbie |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Unique Values with Multiple Criteria | Excel Worksheet Functions | |||
| how to count unique values in excel based on multiple criteria | Excel Worksheet Functions | |||
| Count Multiple Criteria | Excel Worksheet Functions | |||
| Multiple conditions and multiple return values | Excel Worksheet Functions | |||
| Count rows based on multiple criteria | Excel Worksheet Functions | |||
Linear Mode
