Ask Your Question
0

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

asked 2012-08-19 21:03:00 +0100

anonymous user

Anonymous

updated 2013-10-08 22:34:51 +0100

manj_k gravatar image

"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 flag offensive 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

3 Answers

Sort by » oldest newest most voted
1

answered 2012-08-19 21:15:32 +0100

m.a.riosv gravatar image

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

edit flag offensive delete link more
2

answered 2012-08-19 21:52:54 +0100

JohnSUN gravatar image

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

{=SUM((A1:A5="Z") * B1:B5 * C1:C5)}
edit flag offensive delete link more
0

answered 2013-02-22 08:59:31 +0100

qubit gravatar image

updated 2013-02-22 10:31:27 +0100

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.
edit flag offensive delete link more

Comments

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 gravatar imageJohnSUN ( 2013-02-22 10:03:27 +0100 )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...

qubit gravatar imagequbit ( 2013-02-22 10:15:22 +0100 )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?

qubit gravatar imagequbit ( 2013-02-22 10:33:44 +0100 )edit

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

JohnSUN gravatar imageJohnSUN ( 2013-02-22 14:16:27 +0100 )edit

Question Tools

Stats

Asked: 2012-08-19 21:03:00 +0100

Seen: 966 times

Last updated: Feb 22 '13