[Solved] Conditional sum of the product of two columns

Discuss the spreadsheet application

[Solved] Conditional sum of the product of two columns

Postby josephj » Fri Jan 11, 2019 6:41 am

How can I do this?

If column A contains 'ABC', then I want to multiply column B by column C and sum it.

I could create a scratchpad column of B*C and conditionally sum that, but can I do it without the scratchpad?

Something like the following would work with D as the scratchpad column.

Code: Select all   Expand viewCollapse view
=SUMIF($A$9:$A$55,"ABC",$D$9:$D$55)

Code: Select all   Expand viewCollapse view
A   B C
ABC 2 3
DEF 4 5
ABC 1 5
XYZ 9 8

yields

(2*3) + (1*5) = 11
Last edited by josephj on Fri Jan 11, 2019 9:35 am, edited 1 time in total.
User avatar
josephj
 
Posts: 45
Joined: Fri Mar 28, 2008 7:29 pm
Location: Northeastern USA

Re: Conditional sum of the product of two columns

Postby FJCC » Fri Jan 11, 2019 6:47 am

This is what the SUMPRODUCT() function is for
Code: Select all   Expand viewCollapse view
=SUMPRODUCT(A9:A55 = "ABC"; B9:B55; C9:C55)
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7091
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests