Sumproduct Average

AceFI

Board Regular
Joined
Apr 20, 2012
Messages
91
afternoon all.

Just chasing some feedback on a sumproduct average style formula

I have;
Col A - Supplier
Col B - Month
Col C - Income

I'm wanting to average Col C based on certain criteria from A and B

i know this isn't it - however similar to.
=sumproduct(--(A:A="Supplier A"),--(Month(B:B)=2),average(C:C))

thanks for your help.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Depending on your version try the AVERAGEIFS function
 
Upvote 0
i'm using the latest MAC version.. ive had a play with average if, but can't seem to work out the multiple criteria?
 
Upvote 0
=average(if((A:A="Supplier A")*(Month(B:B)=2)*(C:C<>""),C:C))

Ctrl + Shift+Enter.
 
Upvote 0
First, it is not a good to use whole-column ranges like A:A, especially in SUMPRODUCT formulas. I presume it is unlikely that you will ever have 1+ million rows of data.

Use limited ranges instead; for example, A1:A10000 if you might have up to 10,000 rows of data.

Second, you cannot use AVERAGEIFS (with an "S") with the MONTH function.

Alternatively, array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=AVERAGE(IF(A1:A10000="Supplier A", IF(MONTH(B1:B10000)=2, C1:C10000)))

Caveat: I am not familiar with Excel for Mac. I assume you can array-enter a formula. If not, perhaps someone else can tell you how to enter the array formula above.

Finally, if all of your dates are in the same year (e.g. 2019), you can use AVERAGEIFS (if it is available in your version) as follows (normally-entered; press just Enter as usual):

=AVERAGEIFS(C1:C10000, A1:A10000, "Supplier A", B1:B10000, ">="&DATE(2019,2,1), B1:B10000, "<"&DATE(2019,3,1))

If would be prudent to wrap IFERROR(...,"") around the AVERAGE or AVERAGEIFS expression, in case no row meets both criteria.
 
Last edited:
Upvote 0
If all else fails, remember that average = sum / count

so
=sumproduct(--(A:A="Supplier A"),--(Month(B:B)=2),C:C)/sumproduct(--(A:A="Supplier A"),--(Month(B:B)=2))
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top