+ Reply to Thread
Results 1 to 5 of 5

Trying to count cells over two columns but only need to know if either column is marked

  1. #1
    Registered User
    Join Date
    12-20-2022
    Location
    Melbourne, Australia
    MS-Off Ver
    Microsoft 365 MSO (Version 2209 Build 16.0.15629.20256) 64-bit
    Posts
    2

    Trying to count cells over two columns but only need to know if either column is marked

    Very excited to part of an Excel Forum and I am hoping that were will be a genius somewhere in the world that can help me with this formula. I have a payroll excel template which shows who worked during the week, who worked on the weekend and who work both (week and weekend). For this example ? I have simplified the table. Please see below.

    Column A Column B Column C
    Monday - Friday Saturday - Sunday
    Employee 1 Yes Yes
    Employee 2
    Employee 3 Yes
    Employee 4 Yes
    Employee 5
    Employee 6 Yes Yes
    Employee 7 Yes Yes
    Employee 8 Yes
    Employee 9 Yes
    Employee 10 Yes Yes

    How many employees are on payroll this week ? The answer is obviously 8 - employees 2 and 5 didn't work at all. But what formula can I use to calculate this for me. If I use a COUNT function it double counts employees that worked both the week and weekend. Thanks in advance for your help ! I am super excited to see what comes back ? after hours of scrolling the internet, I have nothing!! Regards, Allie

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Trying to count cells over two columns but only need to know if either column is marke

    Power Query Solution

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Trying to count cells over two columns but only need to know if either column is marke

    A formula solution. This in D2
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    A
    B
    C
    D
    1
    Monday - Friday
    Saturday - Sunday
    2
    Employee 1
    Yes
    Yes
    8
    3
    Employee 2
    4
    Employee 3
    Yes
    5
    Employee 4
    Yes
    6
    Employee 5
    7
    Employee 6
    Yes
    Yes
    8
    Employee 7
    Yes
    Yes
    9
    Employee 8
    Yes
    10
    Employee 9
    Yes
    11
    Employee 1
    Yes
    Yes
    Last edited by FlameRetired; 12-21-2022 at 02:13 AM. Reason: Came up with a better formula.
    Dave

  4. #4
    Registered User
    Join Date
    12-20-2022
    Location
    Melbourne, Australia
    MS-Off Ver
    Microsoft 365 MSO (Version 2209 Build 16.0.15629.20256) 64-bit
    Posts
    2

    Re: Trying to count cells over two columns but only need to know if either column is marke

    Hello Dave and Alan - you are both wonderful!!!! Oh my gosh - thanks a million. I will work on this today. Wishing you both a lovely Christmas. Kind Regards, Allie

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Trying to count cells over two columns but only need to know if either column is marke

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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. [SOLVED] How to count marked occurrences of a header value
    By TicklyTigger in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-26-2021, 06:23 AM
  2. Cells that have been modified or added are marked with a comment column
    By wk9128 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-17-2020, 10:54 AM
  3. Replies: 2
    Last Post: 08-16-2020, 09:46 PM
  4. [SOLVED] How to count expired cells but only if they aren't marked with YES in another cell
    By SamEliz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2019, 11:46 AM
  5. [SOLVED] Count if/ Count/ IF two columns if adjacent column = value, countif on other column
    By catscats11 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-10-2017, 10:34 AM
  6. [SOLVED] Counting from 2 marked columns
    By rr23724 in forum Excel General
    Replies: 6
    Last Post: 05-12-2015, 09:20 AM
  7. Can be count green marked cells?
    By toplisek in forum Excel General
    Replies: 5
    Last Post: 01-06-2015, 09:36 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