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!
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.
Thanks for your reply.
I attached the sample.xls to the post.
Do you need anything else?
Fabian
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"))
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
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
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
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!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks