Calculating a weighted average in visible cells with an IF variable

[excelisfunforeveryone]

Hello,
I am trying to get Excel to calculate the following:

Weighted average for visible cells for each variable within Column K
Column K: Job #
Column L: Pay Rate
Column M: Frequency
Column N: Pay Rate * Freq (Helper column)(If necessary)

The following formula works for all cells within the worksheet. I need it to be applied to only visible cells after I use the filter

=SUMPRODUCT($M$14:$M$511*$L$14:$L$511*($K$14:$K$511=A1))/SUMIF($K$14:$K$511,A1,$M$14:$M$511)

I am referencing the column K variables by putting them into A1-8 (you see it referenced in A1)

Thanks for your help. It sounds like I have to use some sort of SUBTOTAL function but for the life of me, I can't get it to work.

Also feel free to correct my absolute references above. I'm not entirely sure I need them here.

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	A	3.066667	3.375
2	B	2.666667	2
3	C	3.428571	2
4	D	3.25	#DIV/0!
5	E	#DIV/0!	#DIV/0!
6	F	#DIV/0!	#DIV/0!
7	G	#DIV/0!	#DIV/0!
8	H	#DIV/0!	#DIV/0!
9
10
11
12
13											Job #	Pay Rate	Frequency
14											A	2	1
24											C	2	2
25											A	3	3
26											A	4	4
27											B	2	1
28
Sheet25

Cell Formulas
Range	Formula
B1:B8	B1	=SUMPRODUCT($M$14:$M$511*$L$14:$L$511*($K$14:$K$511=A1))/SUMIF($K$14:$K$511,A1,$M$14:$M$511)
C1:C8	C1	=SUMPRODUCT($M$14:$M$511*$L$14:$L$511*($K$14:$K$511=A1)*SUBTOTAL(103,OFFSET($K$14,ROW($K$14:$K$511)-ROW($K$14),0)))/SUMPRODUCT($M$14:$M$511*($K$14:$K$511=A1)*SUBTOTAL(103,OFFSET($K$14,ROW($K$14:$K$511)-ROW($K$14),0)))

Your formula is in B1, the filtered one is in C1.

 

[excelisfunforeveryone]

Thank you so much! I really appreciate it. This is going to save a lot of people a lot of work and make our product much more accurate