Friends, I am looking for a excel formula which would calculate the count of M in each row by checking the weekdays row for Sat and Sun.
Like in my spreadsheet attached, on the Row 3 i have only 1 M showing under Sat and Sun, so the result should be 1. And similarly in the next row i have 3 M for Sat and Sun, hence the result is showing as 3.
Pls let me know if my question is not clear.. Thank you in advance.
Re: Excel Formula to count #of M on Weekends
1 way..
=COUNTIFS($B$2:$O$2,"Sat",B3:O3,"M")+COUNTIFS($B$2:$O$2,"Sun",B3:O3,"M")
Re: Excel Formula to count #of M on Weekends
my stab at it
= SUM(COUNTIF(INDIRECT({"B4","C4","I4","J4"}), "M"))
Re: Excel Formula to count #of M on Weekends
or with out the indirect
= SUMPRODUCT( (WEEKDAY(COLUMN(B3:O3),2)<3) *(B3:O3="M") )
Re: Excel Formula to count #of M on Weekends
Thank you so much!
Re: Excel Formula to count #of M on Weekends
welcome :sheep:
Re: Excel Formula to count #of M on Weekends
Oh well, now how do i put the below Excel formula in VBA code.. Because i tried recording this and realized that my range is not constant and not giving me the desired result.
=COUNTIFS($B$2:$O$2,"Sat",B3:O3,"M")+COUNTIFS($B$2:$O$2,"Sun",B3:O3,"M")
Re: Excel Formula to count #of M on Weekends
Now is(for sure) an issue for Pike!:surfing::surfing: I have no idea about VBA!
Re: Excel Formula to count #of M on Weekends
why do you need it in vba?
Re: Excel Formula to count #of M on Weekends
best to start a new thread with real examples, explain what you are doing and that you need a vba solution , then the thread title matches the question
Re: Excel Formula to count #of M on Weekends
Started the new thread..
Re: Excel Formula to count #of M on Weekends
New Thread started..
Don’t have an account yet? Register yourself now and be a part of our community!