# sumproduct

• ### 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

• Not when I use that formula:

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

Friday, January 30, 2015 3:40 PM

### 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

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