“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.”
Check out the examples in the Help for the SUM - for your sample
{=SUM((A1:A5="Z") * B1:B5 * C1:C5)}
=SUMPRODUCT(A1:A5=“Z”;B1:B5*C1:C5)
OR
=SUMPRODUCT(A1:A5=“Z”;B1:B5;C1:C5)
I tested the formulae from @mariosv and @JohnSUN above on the following table:
A B C
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:
- Copy “=SUM((A1:A5=“Z”) * B1:B5 * C1:C5)” and paste it into a cell.
- Instead of typing [Enter], type [Ctrl] + [Shift] + [Enter] to turn that into an Array function.
- The curly braces should appear around the function.
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)?
@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…
@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?
You can also press Ctrl+F2 (Function Wizard), paste formula and check Array (left bottom corner)