+ Reply to Thread
Results 1 to 8 of 8

Formula to check the value of the table header in the same column

  1. #1
    Registered User
    Join Date
    12-23-2019
    Location
    Switzerland
    MS-Off Ver
    Excel for Mac (16.32)
    Posts
    4

    Formula to check the value of the table header in the same column

    I need your help creating an excel sheet, that counts the days an employee was sick (f in cell) and then count the different weekdays (monday-saturday) he was sick. So in Cell NQ3 i would like to show the number of f's, that are in a monday column.
    The numbers in the first row are dates, but only the days are displayed.

    For better understanding you can look at the Screenshot i provided.

    Bildschirmfoto 2019-12-23 um 13.01.51.png


    Thanks for your help an a Merry Christmas to all of you!

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    6,997

    Re: Formula to check the value of the table header in the same column

    Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
    There should be a yellow banner at the top of the screen that tells you how to do this.
    Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    12-23-2019
    Location
    Switzerland
    MS-Off Ver
    Excel for Mac (16.32)
    Posts
    4

    Re: Formula to check the value of the table header in the same column

    Thanks for your reply.
    I attached the sample.xls to the post.

    Do you need anything else?

    Fabian
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    6,997

    Re: Formula to check the value of the table header in the same column

    1. Change the day headings in BM2:BS2 from 2 letter days to 3 letter days (e.g. Mon, Tue, Wed etc)
    2.
    in BM3
    =SUMPRODUCT((TEXT($C2:$BK2,"ddd")=BM$2)*($C3:$BK3="f"))

  5. #5
    Registered User
    Join Date
    12-23-2019
    Location
    Switzerland
    MS-Off Ver
    Excel for Mac (16.32)
    Posts
    4

    Re: Formula to check the value of the table header in the same column

    Thank you for your reply, Special-K!

    It's pretty close to what i want. The Problem ist, that the dates in the header need to stay. Do you think it's still possible? With the weekday() function or something like that?

    Thanks!
    Fabian

  6. #6
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    10,965

    Re: Formula to check the value of the table header in the same column

    You can try =SUMPRODUCT((LEFT(TEXT($C2:$BK2,"ddd"),2)=BM$2)*($C3:$BK3="f"))
    BUT your regional settings should be set to "English" to correspond with your values in row 2 starting in the BM column

  7. #7
    Registered User
    Join Date
    12-23-2019
    Location
    Switzerland
    MS-Off Ver
    Excel for Mac (16.32)
    Posts
    4

    Re: Formula to check the value of the table header in the same column

    Hi all

    Thank you for your suggestions, special-k and pepe le mokko. I was able to solve it with your Input.

    I wish you both a happy new year!
    Fabian

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,647

    Re: Formula to check the value of the table header in the same column

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


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ 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] Search nearest value above in table. return header row + header column
    By 323428 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-22-2018, 05:06 AM
  2. Replies: 2
    Last Post: 03-30-2017, 08:54 PM
  3. Replies: 13
    Last Post: 09-29-2016, 08:56 AM
  4. [SOLVED] Find specific value in table and return header row and header column value
    By nelwan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-30-2013, 01:35 AM
  5. Check column header and return value
    By nareshkt in forum Excel General
    Replies: 2
    Last Post: 10-15-2013, 05:21 PM
  6. [SOLVED] Extract the column header given the row header and highest value in a subset of a table
    By pguarino in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-20-2013, 02:05 AM
  7. (TABLE) Help writing formula to display column header of a result
    By scarch05 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 10-15-2011, 04:40 AM

Tags for this Thread

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