Monday, July 17, 2017

yogi_Multi-Criteria Count Based On Entries In Top Row And Left Column And Data In Another Sheet

Google Spreadsheet   Post  #2208
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-17-2017
question by Roy Klossner:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/G3CDV6R0ANc;context-place=mydiscussions

Using Data Validation for dynamic chart with different size range

Hi,

I'm trying to do a count of call days for a google calendar for a given set of dates.  Here is the link: https://docs.google.com/spreadsheets/d/17It8HbfmRSHvnYQgSVDYrAcyB43JCslrl5sg61Y9in4/edit?usp=sharing

I have most of it figured out.  I'm using a script to pull the data from the calendar to google sheets then using the COUNT() function to count how many calls a person has within a given date range.  What I would like to do now is count weekends twice.  
For example, Isaac is on call 2/11/17 (Sat) and 2/12/17 (Sun) so I want that to count for 2 in C4.  I REALLY don't want to manually input all the weekend dates into a COUNTIFS () function so was hoping to use a WEEKDAYS() function to do it automatically.  Can someone help me with a formula that will auto-count weekends with a COUNTIFS() string, given a certain person being on call any given Sat or Sunday?

Thanks!
Roy

P.S. Please don't run the script, the calendar link has been removed
---
Yogi,

Gen = number of times a person is on general call, indicated on the calendar by Gen: Name
Gen Sat/Sun = number of times a person is on general call on any given Saturday or Sunday, indicated on the calendar by Gen: Name
Gen (Second) = number of times a person is second on general call, indicated by the parenthesis.  For example Gen: Roy (Isaac), means Roy is on call for general and Isaac is second on call.
Gen (Second) Sat/Sun = number of times a person is second on general call, indicated by the parenthesis on any given Saturday or Sunday.  
OB =  number of times a person is on OB call, indicated on the calendar by OB: Name
OB Sat/Sun = number of times a person is on OB call on any given Saturday or Sunday, indicated on the calendar by OB: Name 
OB (Second) = number of times a person is second on OB call, indicated by the parenthesis.  For example OB: Roy (Isaac), means Roy is on call for OB and Isaac is second on call. 
OB (Second) Sat/Sun = number of times a person is second on OB call, indicated by the parenthesis on any given Saturday or Sunday.  

Thanks,
Roy