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

1. ## 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. ## 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.

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

I attached the sample.xls to the post.

Do you need anything else?

Fabian

4. ## 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. ## Re: Formula to check the value of the table header in the same column

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. ## 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. ## 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. ## Re: Formula to check the value of the table header in the same column

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