Hi
I would like to formulate a table to count even distribution of leave taking against all day of the week. It doesn't matter that the leave type is specifically - just that all leave types are accounted for in counting the distribution. Worksheet attached (table in purple is unpopulated)
Thanks in advance for any assistance
I cannot see any mocked-up expected results. Fill in at least the top two rows of the table to show the results you are expecting (manually calculated) and add annotations to explain the calculation.
EDIT: Sudden;y my SUMPRODUCT formula is returning all 0s - your days no longer match!
You'll need this:
=SUMPRODUCT((Table25[[Matthew]:[John]]=[@[Leave type]])*(LEFT(Table25[Day],3)=Table5[[#Headers],[Mon]]))
Last edited by AliGW; 06-24-2023 at 04:39 AM.
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hi Ali
I am a litany of apologies today. I buggered up Table 5 by shortening the title to Mon from Monday. But if it is any consolation, I learned 2 things. Don't mess with the table and how to fix it. I understand all of the formula except the 3 after (Day).
I've included the complete mocked up table and hope I have explained my logic in the footnote.
Thank you for all your help
LEFT(Table25[Day],3)
looks at the first 3 characters of the day names in that column, allowing a match!
I'll have a look at the new workbook - thanks.
Hi Ali, thanks for all your help to date. I have stored this particular nugget of info away for use another day. I spent a good while yesterday trying to understand the logic of my worksheet to write the formula but gave up in frustration. Its linking the pesky leave types that has me scuppered! In meetings all day but I will go at it again this evening. As always, any and all assistance gratefully received.....
I spent some time on it yesterday - couldn't get there, I'm afraid. Such a shame you don't have 365 - would be a breeze with that. I'm sure someone will step in - the working week is busier here than weekends.
Hi Ali, you are great to persist. I feel slightly better about my bungling efforts now. If you can't do it no-one can!
On a point of order, my experience has been if you slip down the pecking order or off the first page, you are less likely to attract someone's attention. Is it possible for me to repost this to another sub forum? I definitely don't want to break any rules. And I am curious to see if someone picks it up.
On a happier note I have requested my software to be upgraded to 365.
Thanks again for your generosity
No, that would be duplicate posting, but by bumping it as you just have, it's top of the list again. You are allowed to bump once a day. Ill look at a 365 solution tomorrow.
Now I know what bumping is!
These won't work until you have Office 365 (probably better not to open it until you do).
In B18:
=COUNTA(LET(f,FILTER(FILTER(Table25[[Matthew]:[John]],Table25[[#Headers],[Matthew]:[John]]=$A18),(LEFT(Table25[Day],3)=Table810[[#Headers],[Mon]])*(Table25[Shift]=$B$16)),FILTER(f,(ISNUMBER(MATCH(f,$A$2:$A$13,0))))))
and in I18:
=COUNTA(LET(f,FILTER(FILTER(Table25[[Matthew]:[John]],Table25[[#Headers],[Matthew]:[John]]=$A18),(LEFT(Table25[Day],3)=LEFT(Table810[[#Headers],[Mon2]],3))*(Table25[Shift]=$I$16)),FILTER(f,(ISNUMBER(MATCH(f,$A$2:$A$13,0))))))
and variations thereof.
Hi Ali
it will be a little while before I get O365 but I think it is correct to solve this for now. I'm already looking forward to understanding this when I can update my sheet with the formula. It's a bit sad but I usually write out the formula in syntax so I can comprehend it.
Once again a multitude of thanks for your generosity and patience
That's not sad - it's called learning! Glad to have helped.
If you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) 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 all those who offered help.
Hi Ali
I hope you are keeping well. I marked this query as solved as I did not have O365 and was not in a position to apply your solution. Life has moved on and now I do have O365. However I still can't make the solution work.
Should I start a new thread? I have attached an updated version of the sheet I was working on (or more correctly the sheet you were working on!)
yours hopefully
Siobhan
It's the headings in I7 and P7 - they need to be DAY and NIGHT (singlular) to match the entries in the shift table.
Do you mean this?I also want to change the formula in AH9 to count presences/absences from Table 26 for one calendar month e.g. July. Currently, it is counting from the 1st January
=COUNTIFS(Table26[Henry],AH8,Table26[Date],">="&DATE(2023,7,1),Table26[Date],"<="&DATE(2023,7,31))
Seen, but no response ... Some feedback might have been nice.
Did these two posts solve the issues you had?
And my rookie errors just keep going on and on. No surprises - it works straightaway. You have the patience of a saint to endure my lack of attention to detail. I'm not sure I can add to your reputation but I will certainly try.
Very cheeky but changing the formula to count just calendar months in Cell AH9? It is currently =COUNTIFS(Table26[Henry],AH8,Table26[Date],">="&TODAY(), which is counting from the 1st Jan to today. I want adjust the formula to only count the month of July.
Thank you very much for your time and enduring patience...
Did you not read post #15? Does that not address this other issue?
Ali there are not enough red faced emojis to reflect my mortification.
On a happier note my solution was very similar but I didn't reference the table the second time. So I must be learning something. Maybe!
My solution =COUNTIFS(Table26[Henry],AH8,Table26[Date],">="&DATE(2023,7,1),[Date],"<="&DATE(2023,7,31))
Good-oh.
Glad we got there. 365 is a good investment - well done!
Hi Ali
the issue I am having is that if the person has no leave taken I would expect to see 0 in each cell (e.g. B19 to O19). But the cell is recording 1 each time. When the error message flashes up I choose "Restore to Calculated Column Formula" and I see a 0 for a second and then it reverts to 1. Although there is no data to count from Table 25.
If you need further assistance, then remove the SOLVED tag.
Hi Ali
I have marked the thread as unsolved. I appreciate that you are very busy but if you had a little time I woudl be so grateful if you could look at the uploaded sheet.
Thanks in advance Siobhan
This proposal employs pre version 365 functions and helper columns (AF:AI)
Columns AF:AI are populated using:Formula:
Please Login or Register to view this content.
Columns B:O are populated using:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hi JeteMc (I'm not sure what the abbreviation of your name might be)
thank you so much for you invaluable assistance. I am endlessly impressed with the generosity of all the contributors on this site. AliGW in particular provides great help but also teaches along the way.
I wrote your solution out in syntax to make sense of it and applied it my real sheet (which has about 150 employees). Needless to say it works perfectly. I love how excel has so many tricks tucked away in sleeves.
I have one additional request to add to my formula - that Table 810 only counts instances of leave up to today. I've tried to include it in my own cack handed way but of ocurse, to no avail.
Any further assitance very gratefully received
Siobhan
I feel that one of the easiest ways to accomplish this is to change the formula in columns AF:AI to read: =IF(Table25[@[Date]:[Date]]<=TODAY(),COUNTIFS(Table5[[Leave type]:[Leave type]],[@Matthew]),0)
Let us know if you have any questions.
Hi I've translated that to a working sheet which bizarrely works in 1 tab (2023 R) but has caused Table 810 in Tab 2023 D to stop working. I hate to ask again but could you look at my working sheet and see what I am doing wrong??
There were a few issues.
1. The names in columns AJ:AR were followed by two numbers so when the sumproduct formula removed one (see post #24) that still left an extra number and that didn't match the names in A29:A37
2. The formula in column AR was:instead ofFormula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
3. In columns I:Y some of the leave types don't match those in A3:A17 i.e. cell P380 was 0.5AL instead of 0.5 AL (the space makes a difference to Excel)
Note that I didn't go through all cells in columns I:Y to see if they were correct but did spot this one that had a green triangle in the top left corner.
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks