# sumifs for weighted average

1. ## sumifs for weighted average

Hello. I've attached a sample workseet with data for invoices and then the cost. I would like to take a weighted average of the cost for each quarter? How would I do this? Various methods welcome. Thanks.  Register To Reply

2. ## Re: sumifs for weighted average

what are you basing the weight on...
and what are you trying to achieve with this?
=MONTH(B6)

B6 is the number of invoices (I presume?) for a cerat "whatever"?  Register To Reply

3. ## Re: sumifs for weighted average

The weigths are based on the number of invoices (yes, B6). So, for each quarter I would like to have the weighted average using a sumif for each quarter. Thank ye kindly.  Register To Reply

4. ## Re: sumifs for weighted average

I assume this is related to your question here

I'd propose a similar formula to the one I suggested there, i.e.

=SUMPRODUCT((\$B\$2:\$M\$2=O2)*(\$B\$4:\$M\$4=O4),\$B\$6:\$M\$6,\$B\$7:\$M\$7)/SUMIFS(\$B\$7:\$M\$7,\$B\$2:\$M\$2,O2,\$B\$4:\$M\$4,O4)  Register To Reply

5. ## Re: sumifs for weighted average

I'm going to try to use that formula but would it be possible to use a different type of formula instead of sumproduct? Maybe a sumif or something like that.  Register To Reply

6. ## Re: sumifs for weighted average

There's no significantly better alternative - SUMIFS can't be used because you need to multiply two ranges (unless you are prepared to have a helper row which multiplies rows 6 and 7). An "array formula" will do it but to no real advantage, i.e.

=SUM(IF((\$B\$2:\$M\$2=O2)*(\$B\$4:\$M\$4=O4),\$B\$6:\$M\$6*\$B\$7:\$M\$7))/SUMIFS(\$B\$7:\$M\$7,\$B\$2:\$M\$2,O2,\$B\$4:\$M\$4,O4)

confirmed with CTRL+SHIFT+ENTER

or with row 8 used as a helper, i.e. in B8 copied across

=B6*B7

you can use SUMIFS like

=SUMIFS(\$B\$8:\$M\$8,\$B\$2:\$M\$2,O2,\$B\$4:\$M\$4,O4)/SUMIFS(\$B\$7:\$M\$7,\$B\$2:\$M\$2,O2,\$B\$4:\$M\$4,O4)  Register To Reply