averageifs #DIV/0!

[Vally 88]

Hello Guys,

I'm working on a AVERAGEIFS formula, please find below:

=AVERAGEIFS('Arabic Pacing'!$J$32:$J$61,'Arabic Pacing'!$E$32:$E$61,Trends!$A4,'Arabic Pacing'!$J$32:$J$61,">0")
Practically I want to have an average when same day of the week and higher than 0.

Looking inside the formula, where first bit is the metric I want to average and it's formulas (NO ACTUAL NUMBERS), first criteria range is again formulas, 1st criteria is a string, 2nd criteria range is again formulas (it's the same range of the average). I have tried to run the 2 criteria separately and they work but when combining the 2 the results is #DIV/0!. I think that the problem is that the actual criteria aren't numbers but formulas and this is stopping it from working. Could be this the reason? If so there is any way to work around this?

Again thank you very much for all your help!

Valeria

 

[steve the fish]

The easiest way to prove it to yourself is to apply an autofilter and filter by the conditions in the formula. How many results do you get?

 

[Joe4]

If those formulas work seperately it doesnt mean the criteria is met when looked at together.

This statement that Steve made is key. When you put them together, they form an AND situation, not an OR statement.
So, you could have records that meet each criteria when taken separately, but don't meet BOTH criteria at the same time.

For example:

ANIMAL      COLOR
Fish         Blue
Dog          White
Lizard       Green
Fish         Red

If Criteria 1 was ANIMAL is FISH, you have two matching records.
If Criteria 2 was COLOR is GREEN, you have one matching record.
If your criteria was ANIMAL is FISH AND COLOR is GREEN, you have no records matching both criteria.