Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and then regular equation to non selected cells
Ok I have half of this equation working, and I'm hoping someone can help me
finish it off. =SUMIF(W3:W10,"x",Q3:Q10) (***This works***) if Q3 (for exmple) does not have an "x" i want to multiply W3 and H3 and add that total to the above equation. Column H = Pieces Column W= Weight Column Q= determines if Column W is gross or net weight. This seems like it should be simple, I just can't tell what I'm missing. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and then regular equation to non selected cells
=sumproduct(--(Q3:Q10)="x",H3:H10,W3:W10)
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and then regular equation to non selected cells
Sorry messed up the parentheses:
=SUMPRODUCT(--(Q3:Q10="x"),H3:H10,W3:W10) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and then regular equation to non selected cells
How about this:
=SUMPRODUCT(--(W3:W10 < "x"),(H3:H10),(W3:W10))+SUMIF(W3:W10,"x",Q3:Q1 0) -- Kevin Vaughn "mike" wrote: Ok I have half of this equation working, and I'm hoping someone can help me finish it off. =SUMIF(W3:W10,"x",Q3:Q10) (***This works***) if Q3 (for exmple) does not have an "x" i want to multiply W3 and H3 and add that total to the above equation. Column H = Pieces Column W= Weight Column Q= determines if Column W is gross or net weight. This seems like it should be simple, I just can't tell what I'm missing. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and then regular equation to non selected cells
Brian,
First of all thanks for the instant help with this. I goofed on my column descriptions, and can't quite get the formula to do what I want, though I think you are very close to solving it. Columns H Q W row3 25 5 row4 10 40 x row5 5 10 x The formula i need will have the result: 175 row3 20*5=125 row4 40 = 40 (not multiplied b/c of "x" in col W) row5 10 = 10 (not multiplied b/c of "x" in col W) total: 175 I hope that is more of a clear example of the situation I'm trying to solve. Thanks again for any help you can provide. "Brian Taylor" wrote: Sorry messed up the parentheses: =SUMPRODUCT(--(Q3:Q10="x"),H3:H10,W3:W10) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and then regular equation to non selected cells
Kevin
You Got It!!!! If I fix my initial column mistake and edit your formula to read like this... =SUMPRODUCT(--(W3:W10 < "x"),(H3:H10),(Q3:Q10))+SUMIF(W3:W10,"x",Q3:Q1 0) It now works perfectly!!! Thanks to you and Brian for all your help. mike "Kevin Vaughn" wrote: How about this: =SUMPRODUCT(--(W3:W10 < "x"),(H3:H10),(W3:W10))+SUMIF(W3:W10,"x",Q3:Q1 0) -- Kevin Vaughn "mike" wrote: Ok I have half of this equation working, and I'm hoping someone can help me finish it off. =SUMIF(W3:W10,"x",Q3:Q10) (***This works***) if Q3 (for exmple) does not have an "x" i want to multiply W3 and H3 and add that total to the above equation. Column H = Pieces Column W= Weight Column Q= determines if Column W is gross or net weight. This seems like it should be simple, I just can't tell what I'm missing. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and then regular equation to non selected cells
You're welcome. Glad it worked for you.
-- Kevin Vaughn "mike" wrote: Kevin You Got It!!!! If I fix my initial column mistake and edit your formula to read like this... =SUMPRODUCT(--(W3:W10 < "x"),(H3:H10),(Q3:Q10))+SUMIF(W3:W10,"x",Q3:Q1 0) It now works perfectly!!! Thanks to you and Brian for all your help. mike "Kevin Vaughn" wrote: How about this: =SUMPRODUCT(--(W3:W10 < "x"),(H3:H10),(W3:W10))+SUMIF(W3:W10,"x",Q3:Q1 0) -- Kevin Vaughn "mike" wrote: Ok I have half of this equation working, and I'm hoping someone can help me finish it off. =SUMIF(W3:W10,"x",Q3:Q10) (***This works***) if Q3 (for exmple) does not have an "x" i want to multiply W3 and H3 and add that total to the above equation. Column H = Pieces Column W= Weight Column Q= determines if Column W is gross or net weight. This seems like it should be simple, I just can't tell what I'm missing. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|