## conditional sumproduct

418 Views 6 Replies Latest reply: Dec 7, 2012 10:07 PM by Barry
Level 1 (50 points)
Currently Being Moderated
Dec 5, 2012 2:36 PM

need help:

i have three columns. one has a list of fruits which can occur several times (three times apples, two times pears, etc.), the second has a list of prices and the third a list of quantities. how do i get the total value of my apple, i.e. the sum of the products of my apples?

in excel i write the following formula:

=SUMPRODUCT(--(A2:A101="Apples");B2:B101;C2:C101)

in numbers it just fails.

thanx

philip

MacBook Pro, OS X Mountain Lion (10.8.2)
• Level 6 (11,825 points)
Currently Being Moderated
Dec 5, 2012 3:04 PM (in response to PhilipDebbas)

This syntax does not work in Numbers.

You can add an aux column to the table to make this possible like:

the Aux column is set up as:

D2=IF(A2="Apple", B2*C2, 0)

select D2 and fill down as needed.

E6=SUM(D)

Another way still uses and aux column but is more flexible to expand to other fruit:

The header of each aux column (column D in this example) determines whether a 1 or 0 is listed in the corresponding row:

D1=IF(A2=D\$1, 1, 0)

select D1 and fill down.  The first cell in the column contains the name of the fruit to search for in the column.  To get the sum product use:

E4=SUMPRODUCT(B,C, D)

• Level 6 (11,825 points)
Currently Being Moderated
Dec 6, 2012 5:54 AM (in response to PhilipDebbas)

Can you post a respresentative example?

• Level 6 (10,495 points)
Currently Being Moderated
Dec 6, 2012 9:22 PM (in response to PhilipDebbas)

Does the daily rate for a person change or is it always the same for that person?  If it is a constant for each person, you can SUMIF the number of days for that person for the week, do a single lookup to get their rate and multiply by that rate. It's just a thought. I can see cases where a rate might change for someone over the course of a year: overtime in a particular week or during a particular project, a raise during the year, etc.

Otherwise, a solution would be to add 52 more columns to do the rate*days for each person for each week so you can do a simple SUMIF.  You already know this as a solution, I'm just saying it is "only" 52 additional columns, not a completely outrageous number, and the formula for them is cut & pasteable.

• Level 7 (28,740 points)
Currently Being Moderated
Dec 7, 2012 10:07 PM (in response to PhilipDebbas)

Looks to be still doable if each person's rate is constant within the project. Might require going to SUMIFS instead of SUMIF.

Regards,

Barry

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.