none
sumproduct RRS feed

  • Question

  • HI,

    Trying to figure out a SUMPRODUCT IFS formula for the following.

    Would like to SUMPRODUCT Number Widgets x Gross Price - Ans - 1447955.

    Text criteria are both in column D, sum range is column E.

    Tried this, but it returns zero:

    =SUMPRODUCT(--(D39:D61="Number Widgets"),--(D39:D61="Gross Price"),E39:E61)

    Any assistance would be greatly appreciated. Thanks.

    COLUMN D                        COLUMN E

    Number Widgets           4,031
    Net Widget Price         171.36
    Tax 10%            17.14
    Fees           11.40
    Gross Price         199.90
    Number Widgets           4,016
    Net Widget Price         135.55
    Tax 10%            13.55
    Fees           10.80
    Gross Price         159.90



    Friday, January 30, 2015 9:11 AM

Answers

All replies

  • The setup is not suitable for SUMPRODUCT. You can use

    =E39*E43+E45*E49


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 30, 2015 12:07 PM
  • Thanks but i have so many like this and i have to make it automate by month wise.
    Friday, January 30, 2015 1:27 PM
  • Sorry, when I copied your example into Excel, one of the values was messed up, so my attempts at SUMPRODUCT didn't work. You can use

    =SUMPRODUCT((D39:D61="Number widgets")*E39:E61,(D43:D65="Gross Price")*E43:E65)

    or

    =SUMPRODUCT((D39:D61="Number Widgets")*E39:E61,OFFSET(E39:E61,4,0))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 30, 2015 1:56 PM
  • My Table is this

    Column A Col B Col C Col D

    Type  Jun Jul Aug
    Avg. Cost per Trip 1500 1500 1500
    Trips 2 2 2
    Avg. Cost per Trip 1000 1000 1000
    Trips 3 2 2
    Avg. Cost per Trip 1000 1500 1500
    Trips 1 1 2
    Avg. Cost per Trip 1200 1500 2000
    Trips 2 2 1

    Avg. Cost Per Trip x Trips -- (1500*2)+(1000*3)+(1000*1)+(1200*2) = 9400

    Like this i have so many month wise. Please help

    Friday, January 30, 2015 2:26 PM
  • Do you want to calculate the total for each month separately, or the total over all months combined?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 30, 2015 2:36 PM
  • Yes Each month separately....
    Friday, January 30, 2015 2:56 PM
  • For June, you can use

    =SUMPRODUCT(B2:B8*($A$2:$A$8="Avg. Cost per Trip"),B3:B9)

    (Adjust the ranges if they go further down)

    This formula can be filled to the right for the other months.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 30, 2015 3:10 PM
  • Thanks, But it Multiplying Avg.Cost per Trip it self.

    Ex: 1500*1500

    But i want Avg.Cost per Trip * Trips i.e (1500*2)+(1000*3)+(1000*1)+(1200*2) = 9400.

    Thanks..

     
    Friday, January 30, 2015 3:34 PM
  • Not when I use that formula:


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 30, 2015 3:40 PM
  • Thanks a lot Hans you saved me...i am so many nice to see you.....

    I used to work on Excel if i want to add some function in MS EXCEL they take our request ?

    Friday, January 30, 2015 4:12 PM
  • Microsoft does have a place where you can submit suggestions, but the URL is changed all the time, so I have no idea what it currently is. Moreover, 99.99% of all suggestions are ignored...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 30, 2015 4:25 PM