Averageifs

[SRacak]

I have a workbook with multiple tabs. On my Summary Tab I'm trying to calculate various averages from other tabs. Averageifs works great when the "Quantity" is 1 in a column from one of the tabs, but if it's greater than 1 it doesn't calculate properly.

Data Tab
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]SPLC[/TD]
[TD="align: center"]Qty.[/TD]
[TD="align: center"]Job[/TD]
[TD="align: center"]Hrs.[/TD]
[TD="align: center"]Mat'l.[/TD]
[/TR]
[TR]
[TD="align: center"]111111[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1160[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]45.00[/TD]
[/TR]
[TR]
[TD="align: center"]111111[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1160[/TD]
[TD="align: center"]1.0[/TD]
[TD="align: center"]90.00[/TD]
[/TR]
[TR]
[TD="align: center"]111111[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1160[/TD]
[TD="align: center"]0.7[/TD]
[TD="align: center"]49.00[/TD]
[/TR]
[TR]
[TD="align: center"]333333[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1160[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"]35.00[/TD]
[/TR]
[TR]
[TD="align: center"]333333[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1160[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]65.00[/TD]
[/TR]
[TR]
[TD="align: center"]555555[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1160[/TD]
[TD="align: center"]1.0[/TD]
[TD="align: center"]25.00[/TD]
[/TR]
[TR]
[TD="align: center"]555555[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1160[/TD]
[TD="align: center"]1.8[/TD]
[TD="align: center"]50.00[/TD]
[/TR]
[TR]
[TD="align: center"]555555[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1160[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"]30.00[/TD]
[/TR]
</tbody>[/TABLE]


Summary Tab
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]SPLC[/TD]
[TD="align: center"]Job[/TD]
[TD="align: center"]Avg. Hrs.[/TD]
[TD="align: center"]Avg. Mat'l.[/TD]
[/TR]
[TR]
[TD="align: center"]111111[/TD]
[TD="align: center"]1160[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]333333[/TD]
[TD="align: center"]1160[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]555555[/TD]
[TD="align: center"]1160[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

​

I was considering an Array formula, but got lost in the translations found on the web...

Any assistance will be greatly appreciated.

Thank you,
Steve

 

[Oaktree]

Try this in 'Summary Tab'!C2:

=SUMPRODUCT(--('Data Tab'!$A$2:$A$9=$A2),'Data Tab'!$B$2:$B$9,'Data Tab'!D$2:D$9)/SUMPRODUCT(--('Data Tab'!$A$2:$A$9=$A2),'Data Tab'!$B$2:$B$9)

copied down and right.

 

[SRacak]

I guess I should have mentioned that I have 23 different SPLCs, ~300 different Jobs and ~81,000 rows of data in my Data Tab. Do you think an Array formula would be more efficient?

Thank you,
Steve

 

[Aladin Akyurek]

A formula with SUMPRODUCT is an array-processing formula. Generally, they are more expensive than the range-processing (database) formulas.

The denominator of the formula Oaktree suggests can be replaced with a faster SumIfs expression:

=SUMPRODUCT(--('Data Tab'!$A$2:$A$9=$A2),'Data Tab'!$B$2:$B$9,'Data Tab'!D$2:D$9)/SUMIFS('Data Tab'!$B$2:$B$9,'Data Tab'!$A$2:$A$9,$A2)

 

[SRacak]

Aladin,

Using your formula, the result was 0.8 Avg. Hrs. for SPLC 111111. Calculating manually, the result is 0.55.

What am I missing?

Thanks,
Steve

 

[Aladin Akyurek]

Aladin,

Using your formula, the result was 0.8 Avg. Hrs. for SPLC 111111. Calculating manually, the result is 0.55.

What am I missing?

Thanks,
Steve

Is this what we need?

=SUMPRODUCT(--('Data Tab'!$A$2:$A$9=$A2),'Data Tab'!$B$2:$B$9,--('Data Tab'!$C$2:$C$9=$B2),'Data Tab'!D$2:D$9)/SUMIFS('Data Tab'!$B$2:$B$9,'Data Tab'!$A$2:$A$9,$A2,'Data Tab'!$C$2:$C$9,$B2)

 

[SRacak]

That formula returned a #REF !...

 

[Aladin Akyurek]

That formula returned a #REF !...

Book1
	A	B	C	D	E
1	SPLC	Qty.	Job	Hrs.	Mat'l.
2	111111	1	1160	0.5	45
3	111111	2	1160	1	90
4	111111	1	1160	0.7	49
5	333333	1	1160	0.3	35
6	333333	2	1160	0.5	65
7	555555	1	1160	1	25
8	555555	2	1160	1.8	50
9	555555	1	1160	0.8	30
Data Tab

Book1
	A	B	C	D
1	SPLC	Job	Avg. Hrs.	Avg. Mat'l.
2	111111	1160	0.8	68.5
3	333333	1160	0.4333333	55
4	555555	1160	1.35	38.75
Summary

You should have shown which values must obtain from Data Tab in your initial post. That said...

In C2 of Summary enter, copy across, and down:

=SUMPRODUCT(--('Data Tab'!$A$2:$A$9=$A2),'Data Tab'!$B$2:$B$9,--('Data Tab'!$C$2:$C$9=$B2),'Data Tab'!D$2:D$9)/SUMIFS('Data Tab'!$B$2:$B$9,'Data Tab'!$A$2:$A$9,$A2,'Data Tab'!$C$2:$C$9,$B2)

Note. This just expands Oaktree's suggestion with the Job condition.

 

[SRacak]

Thank you so much!

 

[Aladin Akyurek]

Thank you so much!

You are welcome.