LinkBack URL
About LinkBacks
Hi guys
Just need to get a formula for sum and countifs for data figures during weekdays.
Attached is the spreadsheet for reference.
Many thanks for help in advance!
Hi guys
Just need to get a formula for sum and countifs for data figures during weekdays.
Attached is the spreadsheet for reference.
Many thanks for help in advance!
Try this..
For Count (excl weekends)
=SUMPRODUCT(--(WEEKDAY(D4:Q4)<>1),--(WEEKDAY(D4:Q4)<>7))
For Sum (excl weekends)
=SUMPRODUCT(--(WEEKDAY(D4:Q4)<>1),--(WEEKDAY(D4:Q4)<>7),D5:Q5)
Life's a spreadsheet, Excel!
Say thanks, Click *
Thanks again Ace! Always helpful! Can I ask for another help? with regard to Average?
Hi Ace, I have attached the spreadsheet with description, hope you can take a look. Thanks
Whats the logic in Week 40 equating to Aug 1 - Sep 28?
Also, do you want to include/exclude weekends?
Hi Ace, we only start tracking the calls from August onwards, and week 40 falls until sep 28. Also exclude weekends. Hope it clarifies the above.
Try this...
This will lead to skewed results due to Nil calls in period prior to Aug 1.
=SUMPRODUCT(--('Total Htls Combined'!B7:NC7<=Parameters!$B$3),--(WEEKDAY('Total Htls Combined'!B5:NC5)<>1),--(WEEKDAY('Total Htls Combined'!B5:NC5)<>7),'Total Htls Combined'!B9:NC9)/SUMPRODUCT(--('Total Htls Combined'!B7:NC7<=Parameters!$B$3),--(WEEKDAY('Total Htls Combined'!B5:NC5)<>1),--(WEEKDAY('Total Htls Combined'!B5:NC5)<>7))
If you want to explicitly exclude these prior calls use the following one
=SUMPRODUCT(--('Total Htls Combined'!B5:NC5>=DATE(2012,8,1)),--('Total Htls Combined'!B7:NC7<=Parameters!$B$3),--(WEEKDAY('Total Htls Combined'!B5:NC5)<>1),--(WEEKDAY('Total Htls Combined'!B5:NC5)<>7),'Total Htls Combined'!B9:NC9)/SUMPRODUCT(--('Total Htls Combined'!B5:NC5>=DATE(2012,8,1)),--('Total Htls Combined'!B7:NC7<=Parameters!$B$3),--(WEEKDAY('Total Htls Combined'!B5:NC5)<>1),--(WEEKDAY('Total Htls Combined'!B5:NC5)<>7))
Thanks, Ace! Works perfectly!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks