Home 
Search 
Today's Posts 
#1




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 D1100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! 
#2




Help in counting and summing cells based on multiple conditions
Try these:
1: =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*(ISNUMBER (D1100))) 2: =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*(D1100)) 3: =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100))) 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 D1100 that have a value in them. *(column D contains numbers.) 2. *Same as number 1 but instead of counting, sum the cells in D1100 that have a value in them. 3. *If the value in cells A1:A100=1, then count the cells in D1100 that have a value in them. *(column D contains numbers.) Any help would be greatly appreciatied! 
#3




Help in counting and summing cells based on multiple conditions
'Try the below
=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")* (ISNUMBER(D1100))) =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1100) =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100))) 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 D1100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! 
#4




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(D1100))) =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1100) =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100))) 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 D1100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! 
#5




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(D1100),0,D1100)) 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(D1100))) =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1100) =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100))) 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 D1100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! 
#6




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(D1100)),D1100) 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(D1100))) =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1100) =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100))) 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 D1100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! 
#7




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 postExcel 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(D1100),0,D1100)) 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(D1100))) =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1100) =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100))) 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 D1100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! 
#8




Help in counting and summing cells based on multiple condition
That formula doesn't need to be arrayentered, 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 postExcel 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(D1100),0,D1100)) 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(D1100))) =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1100) =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100))) 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 D1100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! 
#9




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 postExcel 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(D1100),0,D1100)) 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(D1100))) =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1100) =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100))) 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 D1100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1100 that have a value in them. (column D contains numbers.) Any help would be greatly appreciatied! 
#10




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 mistakemy 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 (D1100))) 2: =SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*(D1100)) 3: =SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100))) 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 D1100 that have a value in them. (column D contains numbers.) 2. Same as number 1 but instead of counting, sum the cells in D1100 that have a value in them. 3. If the value in cells A1:A100=1, then count the cells in D1100 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) 