Sumproduct Average

[AceFI]

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.

 

[arthurbr]

Depending on your version try the AVERAGEIFS function

 

[AceFI]

i'm using the latest MAC version.. ive had a play with average if, but can't seem to work out the multiple criteria?

 

[sandy666]

arthur said AVERAGEIFS

 

[Phuoc]

=average(if((A:A="Supplier A")*(Month(B:B)=2)*(C:C<>""),C:C))

Ctrl + Shift+Enter.

 

[joeu2004]

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.

 

[Jonmo1]

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