| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help in counting and summing cells based on multiple conditions
I have a spreadsheet I use for sales management and need help with 3 formulas
I cannot figure out. Following is what I am trying to do: 1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and the value in cells C1:C100=Prime, count the cells in D1:D100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1:D100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1:D100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help in counting and summing cells based on multiple conditions
Try these:
1: =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*(ISNUMBER (D1:D100))) 2: =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*(D1:D100)) 3: =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1:D100))) Hope this helps. Pete On Nov 9, 11:30*pm, Dave wrote: I have a spreadsheet I use for sales management and need help with 3 formulas I cannot figure out. *Following is what I am trying to do: 1. *If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and the value in cells C1:C100=Prime, count the cells in D1:D100 that have a value in them. *(column D contains numbers.) 2. *Same as number 1 but instead of counting, sum the cells in D1:D100 that have a value in them. 3. *If the value in cells A1:A100=1, then count the cells in D1:D100 that have a value in them. *(column D contains numbers.) Any help would be greatly appreciatied! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help in counting and summing cells based on multiple conditions
'Try the below
=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")* (ISNUMBER(D1:D100))) =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1:D100) =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1:D100))) If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: I have a spreadsheet I use for sales management and need help with 3 formulas I cannot figure out. Following is what I am trying to do: 1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and the value in cells C1:C100=Prime, count the cells in D1:D100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1:D100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1:D100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help in counting and summing cells based on multiple condition
I tried them but I get the #VALUE! error.
"Jacob Skaria" wrote: 'Try the below =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")* (ISNUMBER(D1:D100))) =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1:D100) =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1:D100))) If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: I have a spreadsheet I use for sales management and need help with 3 formulas I cannot figure out. Following is what I am trying to do: 1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and the value in cells C1:C100=Prime, count the cells in D1:D100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1:D100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1:D100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help in counting and summing cells based on multiple condition
I was only able to get a #VALUE error with the second formula, and only if
column D had cells in the formula's range that contained spaces or text. Try this version of the second formula: =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*IF(ISTEXT(D1:D100),0,D1:D100)) Please note that this is an array formula. After entering the formula press CTRL+SHIFT+ENTER instead of just ENTER. If successful, in the Formula Bar you can notice curly braces at both ends like "{=<formula}". Excel adds the curly braces; you can't add them yourself. Hope this helps, Hutch "Dave" wrote: I tried them but I get the #VALUE! error. "Jacob Skaria" wrote: 'Try the below =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")* (ISNUMBER(D1:D100))) =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1:D100) =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1:D100))) If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: I have a spreadsheet I use for sales management and need help with 3 formulas I cannot figure out. Following is what I am trying to do: 1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and the value in cells C1:C100=Prime, count the cells in D1:D100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1:D100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1:D100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help in counting and summing cells based on multiple condition
Hi Dave
Add one more condition to filter for numbers alone..as below =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")* (ISNUMBER(D1:D100)),D1:D100) If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: I tried them but I get the #VALUE! error. "Jacob Skaria" wrote: 'Try the below =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")* (ISNUMBER(D1:D100))) =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1:D100) =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1:D100))) If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: I have a spreadsheet I use for sales management and need help with 3 formulas I cannot figure out. Following is what I am trying to do: 1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and the value in cells C1:C100=Prime, count the cells in D1:D100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1:D100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1:D100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help in counting and summing cells based on multiple condition
I have tried all of the suggestions and still get the error. I am entering
the formula on a TAB named Detailed Activity and referring to cells on a TAB named 2010. Following is a copy of the formula I entered for formula 3: {=SUMPRODUCT(('2010'!$BE$6:$BE$6000=1)*(ISNUMBER(' 2010'!G$6:G$6000)))} I added the { in this post-Excel added them in the formula as you suggested. Any ideas what I am doing wrong? Thanks, "Tom Hutchins" wrote: I was only able to get a #VALUE error with the second formula, and only if column D had cells in the formula's range that contained spaces or text. Try this version of the second formula: =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*IF(ISTEXT(D1:D100),0,D1:D100)) Please note that this is an array formula. After entering the formula press CTRL+SHIFT+ENTER instead of just ENTER. If successful, in the Formula Bar you can notice curly braces at both ends like "{=<formula}". Excel adds the curly braces; you can't add them yourself. Hope this helps, Hutch "Dave" wrote: I tried them but I get the #VALUE! error. "Jacob Skaria" wrote: 'Try the below =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")* (ISNUMBER(D1:D100))) =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1:D100) =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1:D100))) If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: I have a spreadsheet I use for sales management and need help with 3 formulas I cannot figure out. Following is what I am trying to do: 1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and the value in cells C1:C100=Prime, count the cells in D1:D100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1:D100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1:D100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help in counting and summing cells based on multiple condition
That formula doesn't need to be array-entered, and it doesn't need the
dollar signs which you seem to have added. If it gives a #VALUE! error, it is because at least one cell in your range '2010'!$BE$6:$BE$6000 contains a #VALUE! error. Cure that first, and then your formula has a chance to work. -- David Biddulph "Dave" wrote in message ... I have tried all of the suggestions and still get the error. I am entering the formula on a TAB named Detailed Activity and referring to cells on a TAB named 2010. Following is a copy of the formula I entered for formula 3: {=SUMPRODUCT(('2010'!$BE$6:$BE$6000=1)*(ISNUMBER(' 2010'!G$6:G$6000)))} I added the { in this post-Excel added them in the formula as you suggested. Any ideas what I am doing wrong? Thanks, "Tom Hutchins" wrote: I was only able to get a #VALUE error with the second formula, and only if column D had cells in the formula's range that contained spaces or text. Try this version of the second formula: =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*IF(ISTEXT(D1:D100),0,D1:D100)) Please note that this is an array formula. After entering the formula press CTRL+SHIFT+ENTER instead of just ENTER. If successful, in the Formula Bar you can notice curly braces at both ends like "{=<formula}". Excel adds the curly braces; you can't add them yourself. Hope this helps, Hutch "Dave" wrote: I tried them but I get the #VALUE! error. "Jacob Skaria" wrote: 'Try the below =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")* (ISNUMBER(D1:D100))) =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1:D100) =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1:D100))) If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: I have a spreadsheet I use for sales management and need help with 3 formulas I cannot figure out. Following is what I am trying to do: 1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and the value in cells C1:C100=Prime, count the cells in D1:D100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1:D100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1:D100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help in counting and summing cells based on multiple condition
I had only suggested an array formula version of formula 2, because of the IF
function included. Jacob's formula 3 doesn't need to be entered as an array formula. I created a 2010 sheet with values in columns BE & G and your formula worked fine. An error (such as #VALUE) within your BE6:BE6000 range will cause the formula to return that error. Could one of the cells within BE6:BE6000 be returning a #VALUE error? Hutch "Dave" wrote: I have tried all of the suggestions and still get the error. I am entering the formula on a TAB named Detailed Activity and referring to cells on a TAB named 2010. Following is a copy of the formula I entered for formula 3: {=SUMPRODUCT(('2010'!$BE$6:$BE$6000=1)*(ISNUMBER(' 2010'!G$6:G$6000)))} I added the { in this post-Excel added them in the formula as you suggested. Any ideas what I am doing wrong? Thanks, "Tom Hutchins" wrote: I was only able to get a #VALUE error with the second formula, and only if column D had cells in the formula's range that contained spaces or text. Try this version of the second formula: =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*IF(ISTEXT(D1:D100),0,D1:D100)) Please note that this is an array formula. After entering the formula press CTRL+SHIFT+ENTER instead of just ENTER. If successful, in the Formula Bar you can notice curly braces at both ends like "{=<formula}". Excel adds the curly braces; you can't add them yourself. Hope this helps, Hutch "Dave" wrote: I tried them but I get the #VALUE! error. "Jacob Skaria" wrote: 'Try the below =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")* (ISNUMBER(D1:D100))) =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1:D100) =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1:D100))) If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: I have a spreadsheet I use for sales management and need help with 3 formulas I cannot figure out. Following is what I am trying to do: 1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and the value in cells C1:C100=Prime, count the cells in D1:D100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1:D100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1:D100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help in counting and summing cells based on multiple condition
To All that responded-
Thank you! The original reply was what I needed. It was my mistake-my Column A did have #Value! error in it. I did not realize that it would cause the problem. Once I added ISERROR, problem fixed! Thanks again! "Pete_UK" wrote: Try these: 1: =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*(ISNUMBER (D1:D100))) 2: =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*(D1:D100)) 3: =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1:D100))) Hope this helps. Pete On Nov 9, 11:30 pm, Dave wrote: I have a spreadsheet I use for sales management and need help with 3 formulas I cannot figure out. Following is what I am trying to do: 1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and the value in cells C1:C100=Prime, count the cells in D1:D100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1:D100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1:D100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Summing Based on Conditions using Array | Excel Discussion (Misc queries) | |||
| Counting rows based on the contents of multiple cells | Excel Worksheet Functions | |||
| Counting values based on multiple conditions | Excel Worksheet Functions | |||
| Conditionally summing cells based on conditions in other rows | Excel Worksheet Functions | |||
| Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) | |||
Linear Mode
