Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple if conditions - use sumif/sumProduct?
I have a situation where I need to sum a column based on several conditions
in different columns. For every "exp" item (column M), I must only sum (column F) those that contain a "D" or "M" in column E. Below is the forumula I used (and there was a sum product I used), but they either return #VALUE or not the correct number. I've verified the data and used something similar for just 1 argument and that has worked, but using more than 1 is throwing me off. I may have to add more items from column E in the future, so I need to know how to encompass more. Help! =SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")+ ($E1189:$E1316="M"),$F1189:$F1316)) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple if conditions - use sumif/sumProduct?
rirst you shouldn't have a plus sign in the formula it should be another
asterisk. from =SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")+ ($E1189:$E1316="M"),$F1189:$F1316)) to =SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")* $E1189:$E1316="M"),$F1189:$F1316)) Second I don't think you need the IF. Te equal expressions will return a 1 for true and 0 for false. from =SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")* $E1189:$E1316="M"),$F1189:$F1316)) to =SUM($M1189:$M1316="exp")*($E1189:$E1316="D")*(E11 89:$E1316="M")*(1189:$F1316)) Thrd this is an arry formula. You need to enter the formula by typing shift-Ctl-enter which wil add curly brackets around the formula in the F(x) box "macs" wrote: I have a situation where I need to sum a column based on several conditions in different columns. For every "exp" item (column M), I must only sum (column F) those that contain a "D" or "M" in column E. Below is the forumula I used (and there was a sum product I used), but they either return #VALUE or not the correct number. I've verified the data and used something similar for just 1 argument and that has worked, but using more than 1 is throwing me off. I may have to add more items from column E in the future, so I need to know how to encompass more. Help! =SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")+ ($E1189:$E1316="M"),$F1189:$F1316)) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple if conditions - use sumif/sumProduct?
macs wrote:
I have a situation where I need to sum a column based on several conditions in different columns. For every "exp" item (column M), I must only sum (column F) those that contain a "D" or "M" in column E. Below is the forumula I used (and there was a sum product I used), but they either return #VALUE or not the correct number. I've verified the data and used something similar for just 1 argument and that has worked, but using more than 1 is throwing me off. I may have to add more items from column E in the future, so I need to know how to encompass more. Help! =SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")+ ($E1189:$E1316="M"),$F1189:$F1316)) A few things to think about... First, check the logic in your IF: X * Y + Z [X AND Y, OR Z] is not the same as X * (Y + Z) [X AND (Y OR Z)] Second, the SUM(IF([multiple conditions])) construction requires array entry via Ctrl+Shift+Enter. Else, incorrect results. Third, a short list of alternate/OR criteria (Y OR Z) can be abbreviated to something like: ={"D","M"} With all that, here are a new formulas to try (don't forget to press Ctrl+Shift+Enter to commit them): =SUM(IF(($M1189:$M1316="exp")*(($E1189:$E1316="D") +($E1189:$E1316="M")),$F1189:$F1316)) =SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316={"D", "M"),$F1189:$F1316)) or you can use SUMPRODUCT, normally entered: =SUMPRODUCT(($M1189:$M1316="exp")*($E1189:$E1316={ "D","M"})*$F1189:$F1316) Fourth, if there are errors in your data, some of the above formulas will sometimes ignore the errors; at other times the error will propagate to the result. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple if conditions - use sumif/sumProduct?
Joel wrote:
rirst you shouldn't have a plus sign in the formula it should be another asterisk. from =SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")+ ($E1189:$E1316="M"),$F1189:$F1316)) to =SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")* $E1189:$E1316="M"),$F1189:$F1316)) Sure? ($E1189:$E1316="D")*$E1189:$E1316="M" imposes mutually exclusive conditions that will always be FALSE. The OP stated column E could contain "D" OR "M". Nevermind the unmatched parens. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple if conditions - use sumif/sumProduct?
One way...
Normally entered: =SUMPRODUCT(--($M1189:$M1316="exp"),--(ISNUMBER(MATCH($E1189:$E1316,{"D","M"},0))),$F118 9:$F1316) -- Biff Microsoft Excel MVP "macs" wrote in message ... I have a situation where I need to sum a column based on several conditions in different columns. For every "exp" item (column M), I must only sum (column F) those that contain a "D" or "M" in column E. Below is the forumula I used (and there was a sum product I used), but they either return #VALUE or not the correct number. I've verified the data and used something similar for just 1 argument and that has worked, but using more than 1 is throwing me off. I may have to add more items from column E in the future, so I need to know how to encompass more. Help! =SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")+ ($E1189:$E1316="M"),$F1189:$F1316)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF w/multiple conditions | Excel Discussion (Misc queries) | |||
sumif or sumproduct multiple conditions | Excel Discussion (Misc queries) | |||
Multiple Sumif conditions | Excel Worksheet Functions | |||
sumif with multiple conditions | Excel Worksheet Functions | |||
SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec | Excel Discussion (Misc queries) |