I am trying to count the number of occurrences of a value >0 and <=10 from C3:C9330, but I would like to count them dependent on whether the recorded date in B3:B9330 is a weekday or a weekend, i.e. count the number of times a value of 0-10 occurs on weekdays, and how many time it occurs on weekends. I have tried COUNTIFS formulas but I can't seem to get the criteria correct to count only if the date in column B is a weekday (or a weekend). Does anyone know how to do this?
Cheers.
Try these...
For weekdays (Mon - Fri):
=SUMPRODUCT(--(WEEKDAY(B3:B9330,2)<6),--(C3:C9330>0),--(C3:C9330<=10))
For weekends (Sat - Sun):
=SUMPRODUCT(--(WEEKDAY(B3:B9330,2)>5),--(C3:C9330>0),--(C3:C9330<=10))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks for the reply, but it's not what I'm looking for.
I've attached sample data to try explain it better. Essentially I'm looking to count the number of times the value is between 0-10 in column C but dependent on the date in column B being a weekday or a weekend, and then to do this for values between 10-20, 20-30, etc.
Thanks again for the help.
Hmmm...Originally Posted by weldo
That's what you asked for!
You need to change your interval table to be something like this...
Data Range
This formula entered in H3 and copied down:
=SUMPRODUCT(--(WEEKDAY(B$3:B$1002,2)<6),--(C$3:C$1002>F3),--(C$3:C$1002<=G3))
This formula entered in I3 and copied down:
=SUMPRODUCT(--(WEEKDAY(B$3:B$1002,2)>5),--(C$3:C$1002>F3),--(C$3:C$1002<=G3))
If there might be empty cells in the date range then you'll have to add this test to each formula:
--(B$3:B$1002<>"")
Last edited by Tony Valko; 03-26-2014 at 04:12 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks