Hi, help with this problem (sumif)

[dpic]

“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.”

[JohnSUN]

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

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

[mariosv]

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

[qubit1]

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.

[JohnSUN]

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)?

[qubit1]

@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…

[qubit1]

@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?

[JohnSUN]

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