Using WEEKDAY() as a criteria in AVERAGEIFS()

[carwashguy]

I want to average a set of numbers A2:J2, but I only want to include for averaging numbers that also return a 1 on their corresponding A3:J3 cells as well as return a Thursday on their corresponding A1:J1 cells (of which is composed of dates in the form of mm/dd/yyyy).

I tried

Please Login or Register  to view this content.

But, it doesn't work. I can't figure out how to get the WEEKDAY output to play nice with AVERAGEIFS function. Could anyone offer some insight? Much thanks.

[dilipandey]

Hi carwashguy,

Welcome to the forum.

Do you want to average the numbers a2:j2 if they belong to thursdays only and if they have 1 just below them (both the conditions met) ?

Regards,
DILIPandey

<click on below 'star' if this helps>

[dilipandey]

No worries...

Basis my above assumption, I have designed the formula to obtain the desired result. Check and let me know if this is what you were looking for. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[carwashguy]

I see. Thanks dilipandey. {=AVERAGE(IF((WEEKDAY($A$1:$J$1)=5)*($A$3:$J$3=1),$A$2:$J$2,""))} works for what I need.

[dilipandey]

You are welcome....

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

[Mfate]

Hi DILIPandey,

This is almost exactly what I need, but I don't need to consider any values in row 3. I only need to average numbers in row 2 if they fall on a Thursday. I tried to remove the reference to row 3 from the formula {=AVERAGE(IF((WEEKDAY($A$1:$J$1)=5),$A$2:$J$2,""))}, but I get a #VALUE! result.

Any help would be appreciated.

[Mfate]

Update!

I fixed my problem. I wasn't entering the formula as an array, CTRL+SHIFT+ENTER.