+ Reply to Thread
Results 1 to 4 of 4

Counting Weekends that have OFF

  1. #1
    Registered User
    Join Date
    06-17-2020
    Location
    Madrid
    MS-Off Ver
    Office 365
    Posts
    4

    Counting Weekends that have OFF

    Hi! I am stuck trying to add weekends in which the people on my team are OFF(NOT WORKING). But I have tried this formula =SUMPRODUCT((WEEKDAY($E$2:$A$O4,2)>5)*(E23:AQ23="OFF")) But the problem is that sometimes we have split days as OFF so it is not the complete weekend is there a way that I can count only when both days of the weekend are OFF. Thank you!!
    Attached Files Attached Files
    Last edited by pipen319; 06-18-2020 at 06:26 AM.

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,377

    Re: Counting Weekends that have OFF

    Please attach a sample as per the yellow banner, with the desired result. do you just wish to count full weekends? your ranges are also not the same lengths., weekday needs to be weekday ( ,2) to make the week start on a monday

    sumproduct((weekday(range,2)=6)*(range="off")*(range offset by 1="off")) is along the lines of the solution, but attach a sample, I can be no more specific

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,377

    Re: Counting Weekends that have OFF

    Perhaps
    =SUMPRODUCT((E4:AP4="OFF")*(F4:AQ4="OFF")*(WEEKDAY($E$2:$AP$2)=7)) for a full weekend

  4. #4
    Registered User
    Join Date
    06-17-2020
    Location
    Madrid
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Counting Weekends that have OFF

    Thank you it worked!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. networkdays still counting weekends
    By LHARISIADIS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-11-2019, 03:00 PM
  2. Excel counting weekends in Formula
    By mdAzim in forum Excel General
    Replies: 11
    Last Post: 04-20-2015, 05:28 AM
  3. [SOLVED] Counting work days, not weekends
    By joebell in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-13-2013, 05:45 PM
  4. Counting Formula (Count Weekends Off)
    By uhcord98 in forum Excel General
    Replies: 5
    Last Post: 09-19-2011, 05:06 PM
  5. Counting Weekends Off
    By uhcord98 in forum Excel General
    Replies: 3
    Last Post: 08-10-2011, 10:27 PM
  6. Exclude weekends in date counting
    By Ramzes in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-10-2009, 08:57 AM
  7. How to Calculate Dates without counting the weekends
    By Lillian F in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-24-2005, 06:07 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1