averageif spill error

Please try at

F40
=SUMPRODUCT(--(TEXT($B$5:$B$35,"dddd")=A40),$C$5:$C$35)/SUMPRODUCT((TEXT($B$5:$B$35,"dddd")=A40)*($C$5:$C$35<>""))

or with a helper column
D4
=TEXT(B5,"dddd")

G40
=AVERAGEIFS($C$5:$C$35,$D$5:$D$35,A40)

Originally Posted by Bo_Ry

Please try at

F40
=SUMPRODUCT(--(TEXT($B$5:$B$35,"dddd")=A40),$C$5:$C$35)/SUMPRODUCT((TEXT($B$5:$B$35,"dddd")=A40)*($C$5:$C$35<>""))

or with a helper column
D4
=TEXT(B5,"dddd")

G40
=AVERAGEIFS($C$5:$C$35,$D$5:$D$35,A40)

It certainly works, and it's genius! had a go through it with formula evaluate, didn't know you could do that with text. are there any other text values you can convert to numbers or is it just true/false=binary? sidebar.

Secondly, Sorry! seems I oversimplified the example. was expecting an averageif, or maybe sumif/(sumif or countif) so I could just add the extra column of criteria and turn them into ...ifS
So, on the D col I have a drop down select with four options. if a specific one of them is selected, I DON'T want to include that day's values in the calculation. For ease, let's put the options on d:1-4 and the one to exclude on d1.

-- ,*1 is for change boolean true, false to 1,0 for sumproduct to calculate
I would use -- for single-column boolean as this perform faster

Formula:

Please Login or Register  to view this content.

But for multiply-columns boolean, even though -- is faster but * is a bit shorter

Formula:

Please Login or Register  to view this content.

,$C$5:$C$35) column with number should be separated with, comma. this help for Sumproduct to ignore text.

Key "a" in C5 , the formula above still show number but

Formula:

Please Login or Register  to view this content.

*$C$5:$C$35) this will cause error