Using average on Named Range in SUMPRODUCT

**Lifeseeker** · 11-17-2011, 09:07 PM

Hello,

I have a sample SUMPRODUCT formula partially working. What is not working is the average() embedded. While it does return a numeric value, it is way off the mark, and I cannot seem to find out where the problem is.

Please see the attached file.

The problem is in "Overall Reporting", Col F, F1. If you see the formula, I put an Average() on the named range. The p2 and p3 rows do seem to return the right value, but not p 1 row for site A. I'm pretty sure I am just using ONE named range.

The 30.6 doesn't make sense.

Thank you

**ConneXionLost** · 11-17-2011, 09:57 PM

What result are you expecting to see in F2?

Cheers,

**kvsrinivasamurthy** · 11-18-2011, 03:31 AM

Mr lifeseeker,

I have verified the formla .It is working fine.
In Sheet A Data Entry,
The average of col D is 5.1 and thee are 5 rows which satisfies your condition (colC=p1 ,colF=A)
Sumproduct gives 5.1*6=30.6.

Pl clarify what actually you want.

**Deamo** · 11-18-2011, 04:38 AM

Based on the formula you're using, you're looking for the average of the days where reporting period is p 1, p 2, p 3, etc & site is A,b,c, etc ?

If that's the case, try replacing your sumproduct formula with:

=AVERAGEIFS(RangAMean,RangARP,$B2,RangASite,$E2)

Updating the range references as necessary

**Lifeseeker** · 11-18-2011, 03:04 PM

Hi,

I have tried to follow this formula suggested by Deamo and it works out great.

=AVERAGEIFS(RangAMean,RangARP,$B2,RangASite,$E2)

As I expanded the formula to accommodate for 1 additional site, I did this

=AVERAGEIFS(RangAMean,RangARP,$B2,RangASite,$E2)+AVERAGEIFS(RangBMean,RangBRP,$B2,RangBSite,$E2)

When I hit enter, I get division by zero error.

Maybe I need to redefine the named range or I cannot just add two AVERAGEIFS together as if using SUMPRODUCT?

Thank you

**daddylonglegs** · 11-18-2011, 03:14 PM

You get #DIV/0! because at least one of the AVERAGEIFS formulas has no matching rows, you can avoid that by wrapping in an IFERROR function.....

What are you trying to achieve by adding two averages, though? I don't see how that will give you any meaningful result?

**Lifeseeker** · 11-18-2011, 03:24 PM

Bottomline is that I have data coming from two additional sheets, and the average calculation is needed to bring results from them as well.

If I got rid of the 2nd averageifs then it's working with no problems, but not when I added in the 2nd averageifs.

I wasn't sure if I was supposed to add the two averageifs either, but the idea is that I need to have the average calculation done on data coming from worksheet 2 and worksheet 3 and at the end put them all in "overall reporting" worksheet.

**daddylonglegs** · 11-18-2011, 03:55 PM

But adding 2 averages isn't the same as averaging the whole group, e.g.

If you have 2 groups of numbers, 1, 4 and 7 in the first....and you average those then that gives you 4. If I have another group 10 & 18 then the average of those two is 14. Adding the two averages gives you 4+14 =18. That number doesn't really represent anything. The average of all 5 numbers is the sum of them all divided by the count of them all

40/5 = 8

You can use the same approach here, add all the numbers that meet the criteria and divide by the count of rows which meet the criteria, i.e.

=SUMIFS(RangAMean,RangARP,$B2,RangASite,$E2)+SUMIFS(RangBMean,RangBRP,$B2,RangBSite,$E2))/MAX(1,COUNTIFS(RangARP,$B2,RangASite,$E2)+COUNTIFS(RangBRP,$B2,RangBSite,$E2))