Hi all
Having some issues with sumproduct....the formula works fine, however for average purposes, because I am counting weekends, my average is skewed.
can someone pls help me figure this one guy.
=SUMPRODUCT(--($E$6:$J$6>=$C$26)*($E$6:$J$6<=$C$27)*($C$7:$C$10=$C20),$E$7:$J$10)/SUMPRODUCT(--($E$6:$J$6>=$C$26)*($E$6:$J$6<=$C$27)*($C$7:$C$10=$C20))
My formula is saying, if start of month = C26
and select date = C27
then match name in range C7:C10 and give me the average
as you can see in my attached worksheet, C16 is the date cell...and i am using C26 and C27 as helper cells, however the issue is, my formula does not factor in weekends.
My answer in cell E20 should be 107.25 , however since my formula factors in all days, it gives 71.5
can someone pls help me out? thxs alot.
Bookmarks