# Hi, help with this problem (sumif) [closed]

Anonymous

"Ok so, I want to sum the values where A = "Z", however I want it to sum C*B for each instance of "Z". I've tried sumif(A1:A5,"Z",B1:B5 * C1:C5) but this obviously doesn't work. I'd appreciate some help on how i can accomplish this."

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-10-23 02:08:43.874158

Sort by » oldest newest most voted

=SUMPRODUCT(A1:A5="Z";B1:B5*C1:C5) OR =SUMPRODUCT(A1:A5="Z";B1:B5;C1:C5)

more

Check out the examples in the Help for the SUM - for your sample

{=SUM((A1:A5="Z") * B1:B5 * C1:C5)}

more

I tested the formulae from @mariosv and @JohnSUN above on the following table:

  A     B      C
----------------
Z     1      3
Z     2      5
f     4      3
s     8      5
3    16      3


The results are as follows:

{=SUM((A1:A5="Z") * B1:B5 * C1:C5)}  --> 13

=SUMPRODUCT(A1:A5="Z";B1:B5*C1:C5)   --> 13
=SUMPRODUCT(A1:A5="Z";B1:B5;C1:C5)   --> 13


Note: For those unfamiliar with Array Formulae (e.g. JohnSUN's formula above), please refer to the help document here. Specifically, you cannot copy and paste that formula as-is -- it will not be recognized. Here are the steps to use it:

1. Copy "=SUM((A1:A5="Z") * B1:B5 * C1:C5)" and paste it into a cell.
2. Instead of typing [Enter], type [Ctrl] + [Shift] + [Enter] to turn that into an Array function.
3. The curly braces should appear around the function.
more

Formula {=SUM((A1:A5="Z") * B1:B5 * C1:C5)} return value 3?!! But it's impossible... Do you insert ARRAY FORMULA (with Ctrl+Shif+Enter)?

( 2013-02-22 10:03:27 +0200 )edit

@JohnSUN -- I just copy/pasted the formula into Calc. Do the curly braces have an effect on the formula? I assumed that all formulae started with "="...

I just tried CTRL-SHIFT-ENTER, and that didn't seem to insert the formula. Going to read more docs now...

( 2013-02-22 10:15:22 +0200 )edit

@JohnSUN -- updated with more information about how to use an Array Formula. Is there some way to copy/paste those in directly, or do you always have to use Ctrl-Shift-Enter?

( 2013-02-22 10:33:44 +0200 )edit

You can also press Ctrl+F2 (Function Wizard), paste formula and check Array (left bottom corner)

( 2013-02-22 14:16:27 +0200 )edit