automating sumproduct based on dynamic total (a sumif formula)

**jim808** · 08-16-2016, 04:15 AM

The objective is to automate sumproduct via multiple logic. Let me explain:

Fruit .... Total ...... average price
apple .... 17 ... $3.24 ....( =sumproduct(D10:D12,E10:E12)/17)
pear ..... 14 .... $4.57

*17 apple total is based on sumif formula

(fruit) (Quantity ) Price
apple... 10... $2.00
apple ... 2 ... $ 5.00
apple ... 5... $ 5.00
pear ... 4 ... $ 3.50
pear ... 10 ... $ 5.00

Instead of manually typing in a sumproduct formula to find the weighted average price I would like to automate it.

At present to find average price for apple I would add up quantity times price (10*$2)+(2*$5)+(5*$5) then
divide this by sum of apple quantity (10+2+5). I do this via a sumproduct formula.

I think this could be achieved by comparing the sum of apples at each row....(quantity 5 is less than 17(total) so then add 2....is 7 less than 17 yes...and so on...... to the total I have for apples (17).
This is a dynamic total. So tomorrow the total could be 45 and the sumproduct formula would have to adjust.

Any thoughts on automating this via a macro would be appreciated.

**DBY** · 08-16-2016, 08:14 AM

Hello
I don't understand why you're having to manually change the SUMPRODUCT formula. Could you not link the divisor '17' to the cell with the SUMIF formula.

Screenshot.jpg

C2 would have the formula:

Formula:

Please Login or Register  to view this content.

This will change as your values in the table change. The table could be made dynamic to accept new data.

DBY

**jim808** · 08-16-2016, 09:40 AM

Sorry it was simplified. I have an extra row for dates. So for example :

1/2/2016 ....apple.....2....$4
2/8/2016 ....apple.....2....$5
2/8/2016 ....apple.....10...$6

I only want to get sumproduct of the most recent price judging from the date column.
sumif would give total 14 for apple but assuming the last two row for apple was latest date

I would not want to include the row with date 1/2/2016 in the sumproduct formula.

**DBY** · 08-16-2016, 02:01 PM

You can get the Max date of each fruit and do a calculation from that. For the Sumifs:

Formula:

Please Login or Register  to view this content.

and the SUMPRODUCT:

Formula:

Please Login or Register  to view this content.

You could also use a Pivot table by selecting the last date, see screenshot:

Screenshot.jpg

**jim808** · 08-17-2016, 02:06 AM

Thank you DBY. But I would still encounter problems with this approach . For example.

Fruit .... Total ...... average price
apple .... 14 ... ............???...

*14 apple total is based on sumif formula

(fruit) (Quantity ) Price
1/8/2016.....apple....(-1).....1.3
2/8/2016 ....apple.....5....$1.2
2/8/2016 ....apple.....10....$1.4

If an apple is sold its quantity is stated with a minus sign in front in this case -1.
If total is positive number only positive quantity should be included in formula for sumproduct and vice versa.

In this case divisor would be 14 but it should be 15 as sumproduct should only be performed on these two rows:

2/8/2016 ....apple.....5....$1.2
2/8/2016 ....apple.....10....$1.4

Thank you for reading.

**DBY** · 08-17-2016, 05:18 AM

You seem to be adding extra criteria with each post, so we're going round in circles here. It perhaps would be better if you could upload a sample workbook showing some data and various anticipated outcomes.

DBY

**jim808** · 08-17-2016, 05:35 AM

Hi sorry that would be all the criteria.

I had omitted the detail previously as I thought it would make it too complex but as I saw
you were able to solve, I provided the full example this time.

**DBY** · 08-17-2016, 02:51 PM

Hi
If you add another criteria to the SUMPRODUCT column G > 0. Do these return the correct values? For the 'Total':

Formula:

Please Login or Register  to view this content.

and the 'Average':

Formula:

Please Login or Register  to view this content.

These ranges are based on my previous screenshot attachment.

DBY

**jim808** · 08-17-2016, 10:12 PM

Originally Posted by DBY

Hi
If you add another criteria to the SUMPRODUCT column G > 0. Do these return the correct values? For the 'Total':

Formula:

Please Login or Register  to view this content.

and the 'Average':

Formula:

Please Login or Register  to view this content.

These ranges are based on my previous screenshot attachment.

DBY

Yes that worked perfectly .Amazing job. Many Thanks!

**jim808** · 08-17-2016, 11:47 PM

Originally Posted by DBY

Hi
If you add another criteria to the SUMPRODUCT column G > 0. Do these return the correct values? For the 'Total':

Formula:

Please Login or Register  to view this content.

and the 'Average':

Formula:

Please Login or Register  to view this content.

These ranges are based on my previous screenshot attachment.

DBY

DBY could you please explain how the formula is working ?

=SUMIFS($G$2:$G$6,$E$2:$E$6,MAX(INDEX(($F$2:$F$6=A2)*($E$2:$E$6),0)),$F$2:$F$6,A2,$G$2:$G$6,">0")

I see that ($F$2:$F$6=A2) is referring to only look at apple(A2) in range F2:F6 but I do not understand how you are only getting
the most recent dates with MAX(INDEX(($F$2:$F$6=A2)*($E$2:$E$6),0)) I see that ($E$2:$E$6 is referring to the range of dates.

Could you please also breakdown this formula:

SUMPRODUCT(($E$2:$E$6=MAX(INDEX(($F$2:$F$6=A10)*($E$2:$E$6),0)))*($F$2:$F$6=A10)*($G$2:$G$6>0)*($G$2:$G$6)*($H$2:$H$6))/B10

Many Thanks

**jim808** · 08-18-2016, 12:12 AM

Originally Posted by DBY

Hi
If you add another criteria to the SUMPRODUCT column G > 0. Do these return the correct values? For the 'Total':

Formula:

Please Login or Register  to view this content.

and the 'Average':

Formula:

Please Login or Register  to view this content.

These ranges are based on my previous screenshot attachment.

DBY

Hi, I just wondered can I also do this for : G < 0 ie if the sumifs total is a minus number then I only want to sumproduct of minus numbers, in G range to get weighted average price of the minus quantities
how would I account for this in same formulas?

Fruit .... Total ...... average price
apple .... -14 ... ............???...

*(-14) apple total is based on sumif formula

date......(fruit)....(Quantity ).....Price
1/8/2016.....apple....1.........$1.3
2/8/2016 ....apple.....(-5)......$1.2
2/8/2016 ....apple.....(-10)......$1.4

something like this IF statement? I am not sure how to incorporate it though , please advise.

IF($G$2:$G$6>0)*($G$2:$G$6)*($H$2:$H$6)), ($G$2:$G$6<0)*($G$2:$G$6)*($H$2:$H$6))

Thanks

**DBY** · 08-18-2016, 09:30 AM

The INDEX function brings back an array of values based on the range criteria. This is what it actually returns:

Formula:

Please Login or Register  to view this content.

The zeros are non matches of 'Apple' in this instance. So put that in to the MAX function and it returns the maximum date relating to 'Apples'. This becomes the match criteria in both the SUMIFS and SUMPRODUCT functions.

With SUMPRODUCT the arguments with '=' return Boolean values True/False, which are coerced to 1's and 0's. As you can see from this table if all arguments are True then you get 1 which then multiplies the other arrays of values to give you a final sum.

0	1	1	0
1	1	1	1
1	1	0	0

Here's a link to a MS help page:

https://support.office.com/en-gb/art...c-4d2145a2fd2e

For the negative values try these. For the Total:

Formula:

Please Login or Register  to view this content.

and for the Average:

Formula:

Please Login or Register  to view this content.

I've named the ranges for ease of reference. Try these out to see if they return the values you'd expect. I hope all this makes some sense.

DBY

**jim808** · 08-18-2016, 11:45 PM

Thank you for your reply and information DBY.

I just had one unforeseen problem I should have mentioned:

Fruit .... Total ...... average price
apple .... -14 ... ............???...

*(-14) apple total is based on sumif formula

date......(fruit)....(Quantity ).....Price
1/8/2016.....apple....1.........$1.3
2/8/2016 ....apple.....(-5)......$1.2
10/8/2016 ....apple.....(-10)......$1.4

as the max function would only take most recent date : 10/8/2016
2/8/2016 ....apple.....(-5)......$1.2
10/8/2016 ....apple.....(-10)......$1.4

But I would want to include 2/8/2016 as this would yield a result closer to *(-14) apple total than only
this row:

10/8/2016 ....apple.....(-10)......$1.4

I wonder how formula should adjust for this please?

**DBY** · 08-19-2016, 05:40 AM

Working with the negative values, if you want to return all dates, then you can remove the references to dates from the formulas:

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

With this:

2/8/2016 ....apple.....(-5)......$1.2
10/8/2016 ....apple.....(-10)......$1.4

Returns average price of $1.33 with a count of 15. Is this correct?

automating sumproduct based on dynamic total (a sumif formula)

LinkBack

Thread Tools

Rate This Thread

Display

automating sumproduct based on dynamic total (a sumif formula)

Re: automating sumproduct based on dynamic total (a sumif formula)

Re: automating sumproduct based on dynamic total (a sumif formula)

Re: automating sumproduct based on dynamic total (a sumif formula)

Re: automating sumproduct based on dynamic total (a sumif formula)

Re: automating sumproduct based on dynamic total (a sumif formula)

Re: automating sumproduct based on dynamic total (a sumif formula)

Re: automating sumproduct based on dynamic total (a sumif formula)

Re: automating sumproduct based on dynamic total (a sumif formula)

Re: automating sumproduct based on dynamic total (a sumif formula)

Re: automating sumproduct based on dynamic total (a sumif formula)

Re: automating sumproduct based on dynamic total (a sumif formula)

Re: automating sumproduct based on dynamic total (a sumif formula)

Re: automating sumproduct based on dynamic total (a sumif formula)

Thread Information

Users Browsing this Thread

Similar Threads

SUMIF or SUMPRODUCT formula to SUM totals based on multiple criteria

[SOLVED] How to total a range based on 3 criteria, using sumif

[SOLVED] Formula to total a sum based on an item (Sumif?)

[SOLVED] SUMPRODUCT(SUMIF(INDIRECT with Dynamic Worksheet Range

sumif based on sumproduct

[SOLVED] Dynamic Ranking (Top 5 Customer) - Sumif Array / Sumproduct - with Criteria, Ignoring #N/A

[SOLVED] sumproduct of total based on condition

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions