SUMIF(s) - Quick Little Query.

[diamondgeezer1974]

Hi All,

This should be a Quickie for you. I'm sure I'm Missing something obvious here.

Column A = Status Column - Either has a value of Yes or is left Blank

Columns B - E have numbers in them

Is there a formula I can write that says if 'Yes' is in column A then Sum up all the numbers in columns B-E?

Ive tried the following :

=SUMIF(A:A,"=Yes",B:E)

trouble is its only giving me the sum of the numbers in Column B only, not all numbers accross columns B & E.

Should i be using some other sort of SUM function?

Cheers

DG

 

[barry houdini]

This is one option

=SUMPRODUCT((A2:A100="Yes")*B2:E100)

all entries in B2:E100 need to be numeric (or blank) or you'll get an error. If you have any text in that range use an array formula

=SUM(IF(A2:A100="Yes",B2:E100))

confirmed with CTRL+SHIFT+ENTER

 

[diamondgeezer1974]

Excellent thanks for this.

I had to use the Array Formula in the end to get this to work.
Don't really like using them as they are a bit processor intensive and make take a while for a calculation operation to perform.