+ Reply to Thread
Results 1 to 39 of 39

Help with formula to count cells if another cells value equals a specific day of the week.

  1. #1
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Help with formula to count cells if another cells value equals a specific day of the week.

    Good morning, I'm looking for a little help with a countif (i think) formula.

    Currently: S23 - S33 contain values = to the amount of labor I need for 1 order
    P23 - P33 contains values = to the start date of those orders.

    Ex: Row 23 = an order, P23 = 5/24(start date of that order, S23 = 6 (number of people I need to run that order)

    This is just one production line, which I have 10 of. What I am trying to do is make a table that shows how much labor I need on any give day of the week based on the values mentioned above.

    Here is what I think I need, I just can't figure it out:

    COUNT the labor in S23:S33 IF the date(s) in P22:P33 is equal to a Monday

    I would then repeat this process for each day of the week Mon-Fri
    I would also repeat it again with different cell ranges for the other production lines.
    But I think once I get one solved I can copy to the rest.

    Thanks in advance!!


    EDIT: What I really need is the formula to reference the dates in column P - find ALL data that meets the criteria (in this case, all dates that equal monday) and then look at column S and choose the highest number.

    Example

    Job 1 - Mon 5/23 - 5
    Job 2 - Mon 5/23 - 6

    The formula would see that there are 2 Monday values (5 and 6) and then choose the greater of them, in this case 6.

    THank you!
    Attached Files Attached Files
    Last edited by vg05; 05-24-2022 at 10:59 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help with CountIf

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    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.

  3. #3
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with CountIf

    Fixed. Thanks.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Thank you.

    There are instructions at the top of the page explaining how to attach your sample workbook.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

  5. #5
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    try the formula:

    =SUM(IF(WEEKDAY($P$23:$P$33,1)=2,$S$23:$S$33,0))

    The "1" denotes a Sunday through Saturday calendar/
    The "2" denotes the 2nd day of the week which in this case is Monday based on the above Sunday-Saturday calendar.
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

  6. #6
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Thank you, this does work for how I asked, unfortunately I asked poorly.

    The issue is, if I have 2 jobs on the same day and on the same line - and 1 job needs 5 people, and the other job needs 6 people; then I need 6 people. Because the 2 jobs cannot run at the same time. I have to run 1 job and then the other after the first is done. So the sum, which is 11, is too many people.

    What I really need is the formula to reference the dates in column P - find ALL data that meets the criteria (in this case, all dates that equal monday) and then look at column S and choose the highest number.

    Example

    Job 1 - Mon 5/23 - 5
    Job 2 - Mon 5/23 - 6

    The formula would see that there are 2 Monday values (5 and 6) and then choose the greater of them, in this case 6.

    I gave some rep because you did help with that I asked - but I also went back and created a sample workbook at the request of Ali. I added it to the original post. THank you!
    Last edited by AliGW; 05-24-2022 at 10:52 AM. Reason: PLEASE don't quote unnecessarily!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Which is a completely different proposition ...

    Instead of long descriptions, always attach a workbook. It's quicker in the long run.

  8. #8
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Well, I wasn't aware of how "count" and "sum" functioned - I didn't say sum because I didn't want them added. - If I knew excel and was good at it, I wouldn't need to ask for so much help.

    And it did take an hour to put this together. Excel is like learning a new language to me haha. I should probably take a class on it.

    Thanks.
    Last edited by AliGW; 05-24-2022 at 10:59 AM. Reason: PLEASE don't quote unnecessarily!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Try this:

    =SUM(MAX(IF(WEEKDAY($F$5:$F$9)=COLUMNS($I13:J13),$E$5:$E$9)))

    Of course, line 2 and line 3 need considering, but there is no data in the sample file, so I can't work out what to do about that.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Well, I wasn't aware of how "count" and "sum" functioned - I didn't say sum because I didn't want them added.
    That wasn't what I meant, and nor was it what I said. I said that attaching a workbook always helps us to help you better, and often quicker. Your long explanations are better replaced by a well-annotated workbook.

    I was actually referring to the addition of the need to identify a MAX number, which makes what is required very different, and that is why thread titles should be descriptive of your issue and not attempt to identify the function needed.

  11. #11
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Quote Originally Posted by AliGW View Post
    =SUM(MAX(IF(WEEKDAY($F$5:$F$9)=COLUMNS($I13:J13),$E$5:$E$9))).
    This worked, and it worked well for all lines. Thank you very much.

    I do have another favor to ask - would you mind breaking down the formula? I'd like to understand the logic behind it, but I don't quite follow how it works.
    Last edited by AliGW; 05-24-2022 at 11:10 AM. Reason: PLEASE don't quote unnecessarily!

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Sure.

    =MAX(IF(WEEKDAY($F$5:$F$9)=COLUMNS($I13:J13),$E$5:$E$9))

    IF(WEEKDAY($F$5:$F$9)=COLUMNS($I13:J13)

    If the weekday of the dates in the F range = 2 (the number of columns), then return matching values in E. 2 is the second day of a week starting Sunday.

    MAX

    Find the maximum of the above.

    The SUM is not in fact needed - you can remove it.

    Glad to have helped.

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

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you 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.

  13. #13
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Thank you.

    So theoretically, Could I change the "=columns($I13:J13)" to "=2" ? And then, when I change to Tuesday it would change to 3, then 4 for wed and so on?
    Last edited by AliGW; 05-24-2022 at 12:02 PM. Reason: PLEASE don't quote unnecessarily!

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Yes and no: you would need to change each one yourself (2 for Monday, 3 for Tuesday, etc.). My version allows you to place the formula once and then drag copy across, so less work for you (it changes dynamically as you drag).

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

  15. #15
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    So I am running into an error - once again due to my faulty explanations and not the help:

    My issue is with the start date. So by using start date only, That only counts the job if it starts on that date. But I didn't think about jobs that run for multiple days.
    The formula I used on my sheet is this: =MAX(IF(WEEKDAY($P$7:$P$19)=2,$S$7:$S$19)) Where column P is the start date column, 2 = Monday, and column S is the labor. And this worked well for all jobs that STARTED on Monday. But I noticed that some jobs would run on Monday AND Tuesday - but my table was showing a "0" on Tuesday because it wasn't in the start date column. So I altered the formula to this:
    =MAX(IF(WEEKDAY($P$7:$Q$19)=2,$S$7:$S$19)) Where column P is the start date, column Q is the end date, 2 is Monday, and S is labor - This helped for those 2 day jobs.

    However I noticed once again, that there are some jobs that run for 3+ days. So column P would be 5/23, column Q would be 5/26. Which means in my table, labor would populate on Monday and Thursday, but Tues/Wed would show a 0 because they are not represented.

    Is there a way I can change this formula yet again so that instead of =2 it contains 2? So that if there is a Monday (2) BETWEEN P7 and Q19 I would get the count?

    Sorry about this.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Provide an updated workbook showing the issue and expected results and I will have a look. I am not going to even attempt to solve it without one - sorry.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    I'm signing off now for the evening. Will look in again tomorrow in case nobody else picks this up.

  18. #18
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    I was working on it, thank you again for taking a look into this. It's frustrating not understanding excel.
    Attached Files Attached Files

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Try this:

    =SUM(MAX(IF((WEEKDAY($F$5:$F$9)<=COLUMNS($I13:J13))*(WEEKDAY($G$5:$G$9)>=COLUMNS($I13:J13)),$E$5:$E$9)))

    You have miscalculated the value in L15, but the formula calculates this correctly (or there's more you are not really making clear, or I just don't get your logic).

    Wed is 6. The last job runs Wed-Fri and needs 6
    Wrong (as far as I understand the logic as explained in post #15). 25 May is Wednesday, and so the maximum for Wednesday is 7.
    Attached Files Attached Files

  20. #20
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Good catch! Thank you! This formula does work better but I am still having one small issue that I can't seem to fix on my own. If I change one of the start dates to the previous week, but leave the end date in this week - it doesn't seem to count. I re-worked the spreadsheet and highlighted/circled the issue that I produced.

    On line 1 I changed the start date of the first job to 5/20, but left the end date as 5/23. The labor number for 5/23 should be 6, but instead it is showing as zero.
    Attached Files Attached Files

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    OK - look, this is a classic case of shifting goalposts. The formula works perfectly for what you said you wanted - I cannot second guess what other requirements you have that you have not told me.

    Before I look again, is there anything else that needs to be considered?

    Each time you tell me that my formula doesn't work becaiuse of something you haven't told me, I have to go back and re-work it. When on top of that there are errors in your expected results, I am having to check more than just my formula.

    Let me know, please, if there is anything else and please confirm that you have double-checked your expected results.

  22. #22
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    I am not trying to "shift the goalposts" but I also do not understand excel as well as you - I cannot always predict what these formulas wont account for until I see them in action. I can only ask for what I know at the time, and presume that things will work.

    I have reviewed my data and my sheet once again. And as far as I can tell (which may not mean much) this is SHOULD BE the last piece that I am missing. But again, that's what it looks like to me and I do not necessarily understand each excel function well enough to be certain that I am correct.

    I apologize for asking you to rework this multiple times. If there are resources that you can recommend so that I can learn excel better I would be happy to check them out.

    Thank you.

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    I can only ask for what I know at the time, and presume that things will work.
    If what you ask for is missing important details that are not covered in your sample data, then I cannot allow for them. Therefore the formula that I write for Excel will not allow for them. Excel is no more able to second guess your needs than I am.

    I am not trying to "shift the goalposts" but I also do not understand excel as well as you
    You do not need to know anything about Excel to be able to provide accurate sample data. The fact is that you have now changed the requirements twice by adding detail that you didn't mention before: presuming that Excel (or indeed I) would realise that those requirements existed without your explicitly having mentioned them is perhaps a little over-optimistic on your part!

    If there are resources that you can recommend so that I can learn excel better I would be happy to check them out.
    There are many, but that's not what this is about. Alll I ask is that you present the requirements in full at the outset. Again, you do not need to understand how Excel works to know what YOUR requirements are.

    I cannot always predict what these formulas wont account for until I see them in action.
    Hopefully you will now understand that Excel won't account for anything you haven't mentioned, so if you haven't mentioned something, it's fairly safe to assume it won't be included in what the formula does (unless you just get lucky).

    I am not having a go at you, but it really is so frustrating when members just don't think through their requirements properly and, instead, drip-feed them in a way that suggests that the formulae offered were at fault.

    I think it best if I step aside and let someone else take this one forward. I appear to have reached the end of my tether. Sorry and good luck with getting this finished.

  24. #24
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Quote Originally Posted by AliGW View Post

    I am not having a go at you, but it really is so frustrating when members just don't think through their requirements properly and, instead, drip-feed them in a way that suggests that the formulae offered were at fault.

    I think it best if I step aside and let someone else take this one forward. I appear to have reached the end of my tether. Sorry and good luck with getting this finished.
    I understand - however not once did I even imply that you or your solutions were at fault., In fact in most of my replies you will see that I take the responsibility for improper explanation or my lack of understanding how certain functions worked. I apologize if I conveyed otherwise. I thank you for the help that you have given me so far. Please enjoy the rest of your day, and hopefully someone else can help me cross the finish line.

  25. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    not once did I even imply that you or your solutions were at fault.
    It was this that made me feel that way:

    This formula does work better but ...
    The formula did everything you'd asked for up to that point.

    Anyway - my head hurts. I'm going to take a break from it now. I have put out a call for assistance to the wider community, so I am sure you'll get it completed.

  26. #26
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    I can take a look at your file under #18

    your text:

    to word this another way:
    to solve for J26 - search the start dates in line 1 (F5:F9)
    IF any of those dates falls on a Monday (F5 and F6 do)
    THEN look at the values in column E in the rows where that is true (row 5 and 6) (cell E5 and E6)
    Whichever of those quantities is higher, is what should go into cell J26
    In this case, E5 > E6 so 6 is what should be in J26

    repeat these steps for Tuesday - but this would be solved in cell K26
    and Wednesday would be solved in L26
    and so on


    My comment: F5 is a Friday, F6 is a Tuesday.

    So I don't get what you want to achieve.

    You get better help if you add a small example. (just row 5 till row 10) and add manualy the expected result in that file.

    After that I will take a look at it again.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  27. #27
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Thanks or having a look.

    I have updated the file with new notes. My apologies - Ali was helping me at first and since it was just her and I, I was only adding new notes and not replacing the old ones as I should have

    Please see updated notes below the formulated table.

    Thanks in advance!
    Attached Files Attached Files

  28. #28
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    This is the same file you already posted. My request is to post another, since the values don't match your desciption (F5 en F6 is not a Monday).

  29. #29
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Apart from a bit of salmon pink shading and yellow highlighting, that looks identical to the last one I saw.

    Just for future reference: sample workbooks are NEVER just for one helper. You should bear in mind that anyone can come in and offer help at any time, so any number of people will be looking at the workbook.

    It's time for me to log off for the evening. Good luck with this!

  30. #30
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Modifying Ali's formula, J14=SUM(MAX(IF(($F$5:$F$9<=J$13)*($G$5:$G$9>=J$13),$E$5:$E$9))), copy across to N14
    but first change J13=SUMPRODUCT(($F$5:$G$9)*(WEEKDAY($F$5:$G$9)=2))+COLUMNS($J$13:J$13)-1, copy across to N13 and format cells as "dddd"

  31. #31
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Thank you all for the help - I think I have to give up on this

    Oeldere - I know that F5/F6 are not Mondays - I changed the notes that reflected that. But G5 IS a Monday.
    Ali - The data is the same because its all I have. The original workbook has 12 different worksheets each with hundreds of rows of data that I filter into a "viewing" sheet by other values. I cannot make a sample that reflects that. This is as close as I can get.
    Joseph - thanks a lot for the idea - the problem is, they way I use the filter function on this sheet I cannot have the date values analyzed by formulas. I tried that and it's how I got stuck the first time.

    Again, thank you all so much for trying to help, I really do appreciate the time and apologize for the frustration and changing needs. I wish I could make an adequate sample, but it would take weeks.

    Should anyone still want to take a stab at this, please read the notes starting at row 39. Long story short - if you can use a formula in ONLY cells J32:N34 to make that table mirror the table in J14:N16 then the problem is solved.

  32. #32
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Combining the formulas in post #30, J14
    Please Login or Register  to view this content.

  33. #33
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Thank you Joseph, This appears to work. When I go into my sample sheet and enter this formula in the table between I30:N34 everything for Line 1 and Line 3 Matches.

    If you don't mind - can you take a look at line 2? I swear I changed everything correctly to match, but I'm getting all 0's and I'm not sure what I'm doing wrong.
    Attached Files Attached Files

  34. #34
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    J14
    Please Login or Register  to view this content.
    j15
    Please Login or Register  to view this content.
    j16
    Please Login or Register  to view this content.

  35. #35
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Sorry for the delay in response. This works perfectly on the sample sheet. I'm going to try to transpose it onto my main sheet today.

    Thanks for all the help Joseph

  36. #36
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    So I have compared these formulas carefully - and while I don't really understand them it looks like everything is the same throughout them except for the target cells. The issue that I am having, is if on the sample I change the start dates for the first jobs on lines 2 and 3 to 5/20 (which is exactly what I did in the previous sample for Line 1) It shows 0 labor rather than the correct number. I assume I am doing something wrong - but if you have a moment could you take a look and see if you can spot my error? New values are highlighted in yellow
    Attached Files Attached Files

  37. #37
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Sorry, can't find a formula to work on cell J13.

    Formula to work on cell J11, J14=SUM(MAX(IF(($F$5:$F$9<=J$11)*($G$5:$G$9>=J$11),$E$5:$E$9))), copy across to N14

    Formula for cell J11=MAX($F$5:$G$9,$F$14:$G$18,$F$23:$G$27)-7-CHOOSE(WEEKDAY(MAX($F$5:$G$9,$F$14:$G$18,$F$23:$G$27)-7,2),0,1,2,3,4,5,6)+COUNTA($J$13:J$13)-1, copy across to N11

  38. #38
    Forum Contributor
    Join Date
    02-03-2020
    Location
    Wisconsin
    MS-Off Ver
    365
    Posts
    150

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    This works! Even the J13 part - I was able to manipulate what you did slightly on my main workbook and it works.

    Thank you Joseph, and everyone else who contributed. Sorry it took so long. But I appreciate everything. Now I just wish I understood any of it maybe one day.

  39. #39
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help with formula to count cells if another cells value equals a specific day of the w

    Glad to have helped.

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

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you 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.

+ 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] Countif on visible cells only & Countif from a cell with multiple entry
    By MushroomJ in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-26-2021, 05:37 AM
  2. [SOLVED] Help with countif formula for calendar (countif any date in table present)
    By KMJ256388 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-25-2021, 12:11 PM
  3. Replies: 5
    Last Post: 08-12-2020, 05:33 PM
  4. Adding a COUNTIF function to an existing IF AND COUNTIF Formula...
    By Ourkid123uk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-24-2018, 10:09 AM
  5. Replies: 8
    Last Post: 05-27-2017, 07:04 AM
  6. Faster if/countif formula for flagging uniques - countif too slow
    By Speshul in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-19-2015, 01:44 PM
  7. Combining a Date Range COUNTIF and a general COUNTIF
    By jacobtom in forum Excel General
    Replies: 1
    Last Post: 09-15-2011, 05:06 PM

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