none
Countifs RRS feed

  • Question

  • Hi : I am trying to calculate number of school holidays from the second worksheet (list of all school holidays) but it will be based on start (A1) and Finish (A2) date from sheet1. What I need to include is exclude Saturdays and Sundays. Is there a way I can include this in the countifs formula below. Please see the link below. Thank you in advance

    https://1drv.ms/x/s!Amc8fiGpDxekhxPA4zcymCYoh4gx

    Thursday, October 31, 2019 10:34 AM

All replies

  • You can use SUMPRODUCT for this:

    =SUMPRODUCT(('School Holidays'!$A$2:$A$226>=$A$1)*('School Holidays'!$A$2:$A$226<=$A$2)*(WEEKDAY('School Holidays'!$A$2:$A$226,2)<=5))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 31, 2019 3:37 PM
  • Hi, 

    Just checking in to see if the information of Hans Vogelaar was helpful. Please let us know if you would like further assistance.

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Friday, November 1, 2019 9:34 AM
    Moderator