Average with multiple criteria from

[zdonner]

This one is turning into a monster that just won't play nice.

This is feeding a chart that is averaging weekly averages together per lead on 3 different projects.

• If Lead Name is Present > If weeknum=G3 > Average Column
• Each Tracker's averaged column is different as they are different sized trackers
• The first Range in each of the SUMIFS are the Averaged columns from their sheets

Currently this returns results BUT it is including rows that match Name & Weeknum but have blanks. Thus causing the numbers to be divided by much higher numbers.

=IFERROR(SUMIFS('CX Tracker'!$AR$3:$AR$189,'CX Tracker'!$C$3:$C$189,"Daniel M",'CX Tracker'!$L$3:$L$189,G3)+SUMIFS('SNV Tracker'!$Y$3:$Y$189,'SNV Tracker'!$C$3:$C$189,"Daniel M",'SNV Tracker'!$G$3:$G$189,G3)+SUMIFS('Audit Tracker'!$X$3:$X$189,'Audit Tracker'!$H$3:$H$189,"Daniel M",'Audit Tracker'!$M$3:$M$189,G3)/(COUNTIFS('SNV Tracker'!$C$3:$C$189,"Daniel M",'SNV Tracker'!$G$3:$G$189,G3)+COUNTIFS('CX Tracker'!$C$3:$C$189,"Daniel M",'CX Tracker'!$L$3:$L$189,G3)+COUNTIFS('Audit Tracker'!$H$3:$H$189,"Daniel M",'Audit Tracker'!$M$3:$M$189,G3)),0)


Thanks in advance.

[oeldere]

http://www.excelforum.com/the-water-...-question.html

[zdonner]

Sample.xlsx

Here is the trimmed spreadsheet.

[zdonner]

Anyone have any help than can offer?

Unless there is just a more streamlined way to do what I'm attempting, I really just need to make it so that if both criteria are met that the averaged cell is checked and if blank then not counted.

[oeldere]

I see a lot of tabs in your file, but I don't see where the data needs to be filled.

And on what criteria (which cells)?

[zdonner]

Sample_Updated.xlsx

I updated this by highlighting the cells that contain the formula I'm having issues with on the "Chart" Tab.

[zdonner]

Never mind - I figured it out last Friday.

Correct would be;

=IFERROR((SUMIFS('CX Tracker'!$BI$3:$BI$189,'CX Tracker'!$I$3:$I$189,J$2,'CX Tracker'!$L$3:$L$189,$G3)+SUMIFS('CX Tracker'!$BI$3:$BI$189,'CX Tracker'!$O$3:$O$189,J$2,'CX Tracker'!$R$3:$R$189,$G3)+SUMIFS('Audit Tracker'!$X$3:$X$189,'Audit Tracker'!$H$3:$H$189,J$2,'Audit Tracker'!$M$3:$M$189,$G3))/(COUNTIFS('CX Tracker'!$BI$3:$BI$189,">0",'CX Tracker'!$I$3:$I$189,J$2,'CX Tracker'!$L$3:$L$189,$G3)+COUNTIFS('CX Tracker'!$BI$3:$BI$189,">0",'CX Tracker'!$O$3:$O$189,J$2,'CX Tracker'!$R$3:$R$189,$G3)+COUNTIFS('Audit Tracker'!$X$3:$X$189,">0",'Audit Tracker'!$H$3:$H$189,J$2,'Audit Tracker'!$M$3:$M$189,$G3)),"")

Basically I rewrote from scratch after removing a tab from the sheet we decided to combine with another project. That had no effect on this working, but is important to mention when com paring the two.

The issue was the COUNTIFS statements, there were written poorly, I essentially was looking at the problem from the wrong angle.


Thanks,