Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum multiple columns with SUMIF or SUMPRODUCT
I'm running Excel 2003 and am having trouble with what I thought should be a
relatively simple formula. Given a matching criteria in column A, I'd like to sum columns B:D. I've taken a stab at it with the following formulas, but they all return "#Value" 1st try =SUMIF(A1:A3="x","B1:D3) 2nd try =SUMPRODUCT(--(A1:A3="x"),B1:D3) 3rd try =SUMPRODUT((A1:A3="x")*(B1:B3+C1:C3+D1:D3)) Any advice??? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum multiple columns with SUMIF or SUMPRODUCT
Well, at least 2 of the 3 formulae are invalid, so I'm sorry but I don't
believe that they would give you #VALUE! The first has an extra double quote mark, and Excel would have rejected it as an error. The third you have misspelled SUMPRODUCT, so Excel would show a #NAME? error, unless you have defined SUMPRODUT as a UDF. Please don't type into the newsgroup the formulae that you might have thought you were using. If you have a formula on your worksheet, copy from the formula bar and paste into the newsgroup, rather than retyping and getting it wrong. If the 3rd formula, when you've corrected the error, gives #VALUE! error, you must have text instead of numbers somewhere in B1:D3. If you have text which looks like numbers the addition will resolve the numbers, so you must have text which can't be resolved as numbers. -- David Biddulph "TJSea" wrote in message ... I'm running Excel 2003 and am having trouble with what I thought should be a relatively simple formula. Given a matching criteria in column A, I'd like to sum columns B:D. I've taken a stab at it with the following formulas, but they all return "#Value" 1st try =SUMIF(A1:A3="x","B1:D3) 2nd try =SUMPRODUCT(--(A1:A3="x"),B1:D3) 3rd try =SUMPRODUT((A1:A3="x")*(B1:B3+C1:C3+D1:D3)) Any advice??? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum multiple columns with SUMIF or SUMPRODUCT
they all return "#Value"
3rd try =SUMPRODUT((A1:A3="x")*(B1:B3+C1:C3+D1:D3)) Try #3 should work although it can also be written like this: =SUMPRODUCT((A1:A3="x")*B1:D3) If either of those return a #VALUE! error then you either already have #VALUE! errors in the range or there is most likely TEXT in the range B1:D3. Are there any formulas in the range that return formula blanks? -- Biff Microsoft Excel MVP "TJSea" wrote in message ... I'm running Excel 2003 and am having trouble with what I thought should be a relatively simple formula. Given a matching criteria in column A, I'd like to sum columns B:D. I've taken a stab at it with the following formulas, but they all return "#Value" 1st try =SUMIF(A1:A3="x","B1:D3) 2nd try =SUMPRODUCT(--(A1:A3="x"),B1:D3) 3rd try =SUMPRODUT((A1:A3="x")*(B1:B3+C1:C3+D1:D3)) Any advice??? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum multiple columns with SUMIF or SUMPRODUCT
Thank you! I checked for text in the cells and found that was the issue. I
appreciate your help. "T. Valko" wrote: they all return "#Value" 3rd try =SUMPRODUT((A1:A3="x")*(B1:B3+C1:C3+D1:D3)) Try #3 should work although it can also be written like this: =SUMPRODUCT((A1:A3="x")*B1:D3) If either of those return a #VALUE! error then you either already have #VALUE! errors in the range or there is most likely TEXT in the range B1:D3. Are there any formulas in the range that return formula blanks? -- Biff Microsoft Excel MVP "TJSea" wrote in message ... I'm running Excel 2003 and am having trouble with what I thought should be a relatively simple formula. Given a matching criteria in column A, I'd like to sum columns B:D. I've taken a stab at it with the following formulas, but they all return "#Value" 1st try =SUMIF(A1:A3="x","B1:D3) 2nd try =SUMPRODUCT(--(A1:A3="x"),B1:D3) 3rd try =SUMPRODUT((A1:A3="x")*(B1:B3+C1:C3+D1:D3)) Any advice??? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple sumif or sumproduct | Excel Worksheet Functions | |||
multiple if conditions - use sumif/sumProduct? | Excel Discussion (Misc queries) | |||
SUMPRODUCT, SUMIF, COUNTIF for multiple sheets for multiple criter | Excel Worksheet Functions | |||
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
SUMif or SUMproduct across multiple worksheets? | Excel Worksheet Functions |