Skip navigation

conditional sumproduct

433 Views 6 Replies Latest reply: Dec 7, 2012 10:07 PM by Barry RSS
PhilipDebbas Level 1 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)
  • Wayne Contello Level 6 Level 6 (12,660 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:

     

    Screen Shot 2012-12-05 at 4.53.12 PM.png

     

    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:

    Screen Shot 2012-12-05 at 5.01.18 PM.png

    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)

  • Wayne Contello Level 6 Level 6 (12,660 points)
    Currently Being Moderated
    Dec 6, 2012 5:54 AM (in response to PhilipDebbas)

    Can you post a respresentative example?

  • Badunit Level 6 Level 6 (10,765 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.

  • Barry Level 7 Level 7 (29,095 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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

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.