Sum and Coutnifs

[pphg]

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!

[Ace_XL]

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)

[pphg]

Thanks again Ace! Always helpful! Can I ask for another help? with regard to Average?

[pphg]

Hi Ace, I have attached the spreadsheet with description, hope you can take a look. Thanks

[Ace_XL]

Whats the logic in Week 40 equating to Aug 1 - Sep 28?

Also, do you want to include/exclude weekends?

[pphg]

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.

[Ace_XL]

Try this...

Please Login or Register  to view this content.

This will lead to skewed results due to Nil calls in period prior to Aug 1.

If you want to explicitly exclude these prior calls use the following one

Please Login or Register  to view this content.

[pphg]

Thanks, Ace! Works perfectly! Cheers!