Forum Discussion
Solved
How should this formula be put together? I need to countif & divide sum+sum on condition in header
How should this formula be put together?
I have the following formula that I need help with, I need it to count cells with a 1 then divide by the cells with a 1 + cells with a 0 & that works but now I need it to only do it if the $B$1:$GY$1 have "*Monday*" in the cell & then for the rest of the days of the week. I just can't figure out how to make it all contingent on it have is the day of the week in the header cells?
=IFERROR(COUNTIF($B$2:$GY$2,1)/(COUNTIF($B$2:$GY$2,1)+COUNTIF($B$2:$GY$2,0)),NA())
Where should I add this in & how should it be worked? $B$1:$GY$1,"*Monday*"
- I'm not sure if the MONDAY condition is only on the numerator or all cases but basically you should be able to use COUNTIFS()
=IFERROR(COUNTIFS($B$2:$GY$2,1,$B$1:$GY$1,"*Monday*")/(COUNTIFS($B$2:$GY$2,1,$B$1:$GY$1,"*Monday*")+COUNTIFS($B$2:$GY$2,0,$B$1:$GY$1,"*Monday*")),NA())
2 Replies
- I'm not sure if the MONDAY condition is only on the numerator or all cases but basically you should be able to use COUNTIFS()
=IFERROR(COUNTIFS($B$2:$GY$2,1,$B$1:$GY$1,"*Monday*")/(COUNTIFS($B$2:$GY$2,1,$B$1:$GY$1,"*Monday*")+COUNTIFS($B$2:$GY$2,0,$B$1:$GY$1,"*Monday*")),NA())- Thank you, I was trying that but I was missing the ) after the "*Monday*", Thank you again, it works perfectly now.
