| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum column information based on multiple criteria
I've read the posts on using "sumprodut" instead of "sumif", but I'm either
doing something wrong or using "sumproduct" isn't the right tool for what I'm doing. Here's my problem: If $A$8:$A$23 = 660010 and if $B$8:$B$23 = "Ideation", sum $C$8:$C$23. If the value in column A meets my criteria and the value in column C meets my criteria, I want to find the corresponding cell in column C and add them together... Col A Col B Col C 660010 Ideation $5 665511 Ideation $2 660010 Concept $6 660010 Ideation $5 Based on the above information, I would want my formula to return $10, since the first and last rows contain the correct criteria. I've tried many things - sumif (which only allows for one criteria), nested if statements, using ifs and ands....I've been looking at it so hard that my mind has gone blank. Can anyone help? Much appreciated!! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum column information based on multiple criteria
Have you tried entering your formula as an array formula?
-- Brevity is the soul of wit. "GHawkins" wrote: I've read the posts on using "sumprodut" instead of "sumif", but I'm either doing something wrong or using "sumproduct" isn't the right tool for what I'm doing. Here's my problem: If $A$8:$A$23 = 660010 and if $B$8:$B$23 = "Ideation", sum $C$8:$C$23. If the value in column A meets my criteria and the value in column C meets my criteria, I want to find the corresponding cell in column C and add them together... Col A Col B Col C 660010 Ideation $5 665511 Ideation $2 660010 Concept $6 660010 Ideation $5 Based on the above information, I would want my formula to return $10, since the first and last rows contain the correct criteria. I've tried many things - sumif (which only allows for one criteria), nested if statements, using ifs and ands....I've been looking at it so hard that my mind has gone blank. Can anyone help? Much appreciated!! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum column information based on multiple criteria
I tried the following formula:
Col A Col B Col C 660010 Ideation $5 665511 Ideation $2 660010 Concept $6 660010 Ideation $5 =sum(if((A1:A4="660010")*(B1:B4="Ideation"),C1:C4) ), and then using the Ctrl+Shift+Enter to calculate. This returns 0 instead of the 10 I'm expecting. Am I doing something wrong? "Dave F" wrote: Have you tried entering your formula as an array formula? -- Brevity is the soul of wit. "GHawkins" wrote: I've read the posts on using "sumprodut" instead of "sumif", but I'm either doing something wrong or using "sumproduct" isn't the right tool for what I'm doing. Here's my problem: If $A$8:$A$23 = 660010 and if $B$8:$B$23 = "Ideation", sum $C$8:$C$23. If the value in column A meets my criteria and the value in column C meets my criteria, I want to find the corresponding cell in column C and add them together... Col A Col B Col C 660010 Ideation $5 665511 Ideation $2 660010 Concept $6 660010 Ideation $5 Based on the above information, I would want my formula to return $10, since the first and last rows contain the correct criteria. I've tried many things - sumif (which only allows for one criteria), nested if statements, using ifs and ands....I've been looking at it so hard that my mind has gone blank. Can anyone help? Much appreciated!! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum column information based on multiple criteria
Try:
=SUMIF((A1:A4="60010")*(B1:B4="Ideation"),C1:C4) and hit CTRL+SHIFT+ENTER (note the difference in my formula is you're using the SUMIF function not the SUM function as in your formula below). Dave -- Brevity is the soul of wit. "GHawkins" wrote: I tried the following formula: Col A Col B Col C 660010 Ideation $5 665511 Ideation $2 660010 Concept $6 660010 Ideation $5 =sum(if((A1:A4="660010")*(B1:B4="Ideation"),C1:C4) ), and then using the Ctrl+Shift+Enter to calculate. This returns 0 instead of the 10 I'm expecting. Am I doing something wrong? "Dave F" wrote: Have you tried entering your formula as an array formula? -- Brevity is the soul of wit. "GHawkins" wrote: I've read the posts on using "sumprodut" instead of "sumif", but I'm either doing something wrong or using "sumproduct" isn't the right tool for what I'm doing. Here's my problem: If $A$8:$A$23 = 660010 and if $B$8:$B$23 = "Ideation", sum $C$8:$C$23. If the value in column A meets my criteria and the value in column C meets my criteria, I want to find the corresponding cell in column C and add them together... Col A Col B Col C 660010 Ideation $5 665511 Ideation $2 660010 Concept $6 660010 Ideation $5 Based on the above information, I would want my formula to return $10, since the first and last rows contain the correct criteria. I've tried many things - sumif (which only allows for one criteria), nested if statements, using ifs and ands....I've been looking at it so hard that my mind has gone blank. Can anyone help? Much appreciated!! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum column information based on multiple criteria
=sumproduct(--($A$8:$A$23=660010),--($B$8:$B$23="Ideation"),$C$8:$C$23)
if the data in A8:A23 is text, you will need to change the 660010 to "660010". "GHawkins" wrote: I've read the posts on using "sumprodut" instead of "sumif", but I'm either doing something wrong or using "sumproduct" isn't the right tool for what I'm doing. Here's my problem: If $A$8:$A$23 = 660010 and if $B$8:$B$23 = "Ideation", sum $C$8:$C$23. If the value in column A meets my criteria and the value in column C meets my criteria, I want to find the corresponding cell in column C and add them together... Col A Col B Col C 660010 Ideation $5 665511 Ideation $2 660010 Concept $6 660010 Ideation $5 Based on the above information, I would want my formula to return $10, since the first and last rows contain the correct criteria. I've tried many things - sumif (which only allows for one criteria), nested if statements, using ifs and ands....I've been looking at it so hard that my mind has gone blank. Can anyone help? Much appreciated!! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum column information based on multiple criteria
That works like a charm! Is there a way to allow null values in the range? In
the event that A11 is NULL? "lk" wrote: =sumproduct(--($A$8:$A$23=660010),--($B$8:$B$23="Ideation"),$C$8:$C$23) if the data in A8:A23 is text, you will need to change the 660010 to "660010". "GHawkins" wrote: I've read the posts on using "sumprodut" instead of "sumif", but I'm either doing something wrong or using "sumproduct" isn't the right tool for what I'm doing. Here's my problem: If $A$8:$A$23 = 660010 and if $B$8:$B$23 = "Ideation", sum $C$8:$C$23. If the value in column A meets my criteria and the value in column C meets my criteria, I want to find the corresponding cell in column C and add them together... Col A Col B Col C 660010 Ideation $5 665511 Ideation $2 660010 Concept $6 660010 Ideation $5 Based on the above information, I would want my formula to return $10, since the first and last rows contain the correct criteria. I've tried many things - sumif (which only allows for one criteria), nested if statements, using ifs and ands....I've been looking at it so hard that my mind has gone blank. Can anyone help? Much appreciated!! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum column information based on multiple criteria
This does not work - it prompts me that I've entered too few arguments. The
suggestion of using the SUMPRODUCT does end up working (see post from Ik) unless there are null values in my range. "Dave F" wrote: Try: =SUMIF((A1:A4="60010")*(B1:B4="Ideation"),C1:C4) and hit CTRL+SHIFT+ENTER (note the difference in my formula is you're using the SUMIF function not the SUM function as in your formula below). Dave -- Brevity is the soul of wit. "GHawkins" wrote: I tried the following formula: Col A Col B Col C 660010 Ideation $5 665511 Ideation $2 660010 Concept $6 660010 Ideation $5 =sum(if((A1:A4="660010")*(B1:B4="Ideation"),C1:C4) ), and then using the Ctrl+Shift+Enter to calculate. This returns 0 instead of the 10 I'm expecting. Am I doing something wrong? "Dave F" wrote: Have you tried entering your formula as an array formula? -- Brevity is the soul of wit. "GHawkins" wrote: I've read the posts on using "sumprodut" instead of "sumif", but I'm either doing something wrong or using "sumproduct" isn't the right tool for what I'm doing. Here's my problem: If $A$8:$A$23 = 660010 and if $B$8:$B$23 = "Ideation", sum $C$8:$C$23. If the value in column A meets my criteria and the value in column C meets my criteria, I want to find the corresponding cell in column C and add them together... Col A Col B Col C 660010 Ideation $5 665511 Ideation $2 660010 Concept $6 660010 Ideation $5 Based on the above information, I would want my formula to return $10, since the first and last rows contain the correct criteria. I've tried many things - sumif (which only allows for one criteria), nested if statements, using ifs and ands....I've been looking at it so hard that my mind has gone blank. Can anyone help? Much appreciated!! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum column information based on multiple criteria
Sumproduct, with the "--", assigns a value of 1 (if true) and 0 (if false) to
your conditions, then it multiplys the value of the conditions (1's and/or 0's) by your last data range. So if all the conditions are true, you get 1*1*data range (for that row only). If you have a NULL, in your example, it will be assigned a value of zero. (Ssorry this isn't a very eloquent answer. There are lots of websites with more Sumproduct info.) "GHawkins" wrote: That works like a charm! Is there a way to allow null values in the range? In the event that A11 is NULL? "lk" wrote: =sumproduct(--($A$8:$A$23=660010),--($B$8:$B$23="Ideation"),$C$8:$C$23) if the data in A8:A23 is text, you will need to change the 660010 to "660010". "GHawkins" wrote: I've read the posts on using "sumprodut" instead of "sumif", but I'm either doing something wrong or using "sumproduct" isn't the right tool for what I'm doing. Here's my problem: If $A$8:$A$23 = 660010 and if $B$8:$B$23 = "Ideation", sum $C$8:$C$23. If the value in column A meets my criteria and the value in column C meets my criteria, I want to find the corresponding cell in column C and add them together... Col A Col B Col C 660010 Ideation $5 665511 Ideation $2 660010 Concept $6 660010 Ideation $5 Based on the above information, I would want my formula to return $10, since the first and last rows contain the correct criteria. I've tried many things - sumif (which only allows for one criteria), nested if statements, using ifs and ands....I've been looking at it so hard that my mind has gone blank. Can anyone help? Much appreciated!! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum column information based on multiple criteria
That helps a lot - thanks for all the help!
"lk" wrote: Sumproduct, with the "--", assigns a value of 1 (if true) and 0 (if false) to your conditions, then it multiplys the value of the conditions (1's and/or 0's) by your last data range. So if all the conditions are true, you get 1*1*data range (for that row only). If you have a NULL, in your example, it will be assigned a value of zero. (Ssorry this isn't a very eloquent answer. There are lots of websites with more Sumproduct info.) "GHawkins" wrote: That works like a charm! Is there a way to allow null values in the range? In the event that A11 is NULL? "lk" wrote: =sumproduct(--($A$8:$A$23=660010),--($B$8:$B$23="Ideation"),$C$8:$C$23) if the data in A8:A23 is text, you will need to change the 660010 to "660010". "GHawkins" wrote: I've read the posts on using "sumprodut" instead of "sumif", but I'm either doing something wrong or using "sumproduct" isn't the right tool for what I'm doing. Here's my problem: If $A$8:$A$23 = 660010 and if $B$8:$B$23 = "Ideation", sum $C$8:$C$23. If the value in column A meets my criteria and the value in column C meets my criteria, I want to find the corresponding cell in column C and add them together... Col A Col B Col C 660010 Ideation $5 665511 Ideation $2 660010 Concept $6 660010 Ideation $5 Based on the above information, I would want my formula to return $10, since the first and last rows contain the correct criteria. I've tried many things - sumif (which only allows for one criteria), nested if statements, using ifs and ands....I've been looking at it so hard that my mind has gone blank. Can anyone help? Much appreciated!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I make it add a column based on 2 different criteria? | Excel Worksheet Functions | |||
| Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
| Count rows based on multiple criteria | Excel Worksheet Functions | |||
Linear Mode
