sum multiple columns with SUMIF or SUMPRODUCT

[TJSea]

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. I've taken a stab at it with the following formulas, but
they all return "#Value"

1st try =SUMIF(A1:A3="x","B13)
2nd try =SUMPRODUCT(--(A1:A3="x"),B13)
3rd try =SUMPRODUT((A1:A3="x")*(B1:B3+C1:C3+D13))

Any advice???

 

[David Biddulph]

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 B13. 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.

 

[T. Valko]

they all return "#Value"

3rd try =SUMPRODUT((A1:A3="x")*(B1:B3+C1:C3+D13))

Try #3 should work although it can also be written like this:

=SUMPRODUCT((A1:A3="x")*B13)

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 B13.
Are there any formulas in the range that return formula blanks?

 

[TJSea]

Thank you! I checked for text in the cells and found that was the issue. I
appreciate your help.