+ Reply to Thread
Results 1 to 29 of 29

Countif formula with criteria, stop counting if not found, but start count over after

  1. #1
    Registered User
    Join Date
    08-06-2020
    Location
    edmond, oklahoma
    MS-Off Ver
    windows 10
    Posts
    14

    Countif formula with criteria, stop counting if not found, but start count over after

    I need a formula in a pivot table to get totals for assets sitting on the lot for more than 4 days when searching date rages.
    ColumnA - ALL Asset numbers we own
    B1-DATE
    B2:B200-Assets on the yard that day
    C1-DATE
    C2:C200-Assets on the yard that day
    Continued...

    I need to count the days the assets sit on the yard. But start count over when it is not found on the yard.
    I need to be be able to count all the way back from the beginning of the year. But only count days it's been sitting on the yard consecutively. And start count over once it leaves the yard.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,789

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,789

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    and BTW, I dont understand how you can count how long something is there for - if it has already left the yard?

  4. #4
    Registered User
    Join Date
    08-06-2020
    Location
    edmond, oklahoma
    MS-Off Ver
    windows 10
    Posts
    14

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    "and BTW, I dont understand how you can count how long something is there for - if it has already left the yard?"

    I truly want a week analysis of the trucks that sit for 4 days or longer. The assets will leave and sometimes come back
    within the same week. However, My problem is getting it to start the count over once it comes back to the yard.

    Example:
    Asset is on the yard mon, tues, and wed. Not on thurs, but comes back to yard fri and sat.
    I need to not show that asset as sitting 4+ days for the week because it is only current - 2 days on the lot.
    I need to also add the counts from the previous weeks if the asset sits week to week.

    I've tried several formulas and I can not figure out where I'm going wrong. I was trying NOT to do additional daily
    columns to find this.. Instead I was hoping to be able to setup a"weekly" value field I could plug into a pivot table to show this
    info in a clean format. I also want to show the assets that have the most extended stay on yard over quarter periods of the year.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    "and some manually calculated results"
    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.

  6. #6
    Registered User
    Join Date
    08-06-2020
    Location
    edmond, oklahoma
    MS-Off Ver
    windows 10
    Posts
    14

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    What is the solution Oeldere?

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    Solution removed since the pivot table is not correct.

    Edit

    post my solution in #11.
    Last edited by oeldere; 08-11-2020 at 04:33 PM. Reason: Solution removed since the pivot table is not correct.

  8. #8
    Registered User
    Join Date
    08-06-2020
    Location
    edmond, oklahoma
    MS-Off Ver
    windows 10
    Posts
    14

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    The numbers in the green are not correct.

    Please see that the asset numbers in column A on the spreadsheet are only for reference. They do not have a date above them.
    Column A is not counted as days on the lot

    None of the asset totals are correct?
    "PT!"E55 is not 6. Asset 0421 does not show up on the yard all week?

    Please help. Thank you

  9. #9
    Registered User
    Join Date
    08-06-2020
    Location
    edmond, oklahoma
    MS-Off Ver
    windows 10
    Posts
    14

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    FDibbins , Can you help now that I attached a spreadsheet?

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,789

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    Follow the directions in the yellow banner at the top.

    Also, I gave directions to you in post #2

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    See the attached file (pivot table).

    See if this fits your needs.

  12. #12
    Registered User
    Join Date
    08-06-2020
    Location
    edmond, oklahoma
    MS-Off Ver
    windows 10
    Posts
    14

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    Maybe my sheet added will help.

    Please see sheet="Need" in my new attachment.

    I did this with lots of filtering and adding a new column per day which I want to try to avoid. Can you see where I get my number totals now? Can you help change the formulas to show these totals in your pivot table?

    Thank you.
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    I hope I am not muddying the water. I know you said this in post #4:
    "I was trying NOT to do additional daily columns to find this."
    But as nobody has seemingly yet proposed a satisfactory solution, here is a possible answer, although it does require a block of helper columns, one per day - but no manual work beyond that.

    Janae.png

    You have 10 dates in your sample, so I have created 10 helper columns M:V. In M2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Copy across and down over the range M2:V100 (copy down further as needed).
    This simply results in a 0 if the asset was on-yard that day and a 1 otherwise.

    Column-X contains the answer. For example:
    x4 = 4 because there is just one block of at least 4 on-yard zeros.
    x5 = 6 because there is just one block of 6 consecutive on-yard zeros
    x8 = 0 because there are no more than 2 consecutive on-yard days.
    I played with row 89 to test 2 x blocks of 4 days resulting in x89 = 8

    In X2, copied down to X100 or further if required:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    To make column-X work correctly, one more helper column W:W is needed. It is simply set to "1" for all rows.

    I know you didn't want a helper column solution, but hopefully this is of some help, pending a better answer. Let us know.
    Attached Files Attached Files
    Last edited by GeoffW283; 08-11-2020 at 06:32 PM.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  14. #14
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    @Janae

    In #10 you reply without adding the forummember to who you are replying.
    Since you get help of several forummembers, adding the name is very usefull.

    In my example I supposed that the day with numbers are working days and the blanc cells are free (non working) days.
    Is this correct?

    Can you explain which data in the pivot table are not correct and what you are expecting as result.
    Please also add why you expect that result.

  15. #15
    Registered User
    Join Date
    08-06-2020
    Location
    edmond, oklahoma
    MS-Off Ver
    windows 10
    Posts
    14

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    Oeldere,

    Please check the names of the replies. #10 reply is FDibbins. I replied to you in #12.

    No, do not refer to any of the blank cells please

    Please refer to #12 and reopen my new edited attachment. Sheet=Need will tell you the correct numbers(green column) I'm trying get with out all the filtering and separate daily columns I had to create for each one. You will see the difference in my green column and yours.

  16. #16
    Registered User
    Join Date
    08-06-2020
    Location
    edmond, oklahoma
    MS-Off Ver
    windows 10
    Posts
    14

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    GeoffW283,

    I see in your example you chose 0's for on the yard, Which is just switched for my example in the sheet "Need" where I made 1's as the days on the yard. The information I am trying to get is only if the asset is still sitting on the yard. Therefore, If the asset has left the yard it goes back to zero days. And if it returns next week or any other day the count starts over. Please refer to my example in #4 reply.

    thank you.. please help if you can

  17. #17
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    Unfortunately, I developed my solution before I saw your post with the opposite sense for the helper columns. It really doesn't matter though.

    In your "Need" worksheet column L you provide 22 expected results.
    In my attachment to this post my column-X formula agrees with 19 out of 22 of your expected results.
    The only change from my post #13 attachment was extending my formulas down enough to cover all your examples.

    Here are the only 3 discrepancies:

    TRK 86348:
    You say 4, I say 6. Here's the daily data (your convention of 1=on-yard)
    0 1 1 0 1 1 1 1 1 1
    Why is 6 not correct?

    TRK 9533:
    You say 4, I say 9. Here's the daily data (your convention of 1=on-yard)
    1 1 1 1 1 0 1 1 1 1
    There are 5 consecutive on-yard days, then 1 off-yard day, then 4 consecutive on-yard days. Why does this not count as 5 + 4 = 9?

    TRK 0433:
    You say 7, I say 0.
    I don't see "0433" in ANY days of the sample data, hence my zero.
    You do have TRK 433 and 0433a, so maybe something strange is going on in this one case?
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    08-06-2020
    Location
    edmond, oklahoma
    MS-Off Ver
    windows 10
    Posts
    14

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    GeoffW283,

    Correct it doesn't matter if the amounts match. But they do not.

    TRK 86348
    6 is correct - I did my column without a formula so it was a mistake my hand. I've have since corrected it.

    TRK 9533
    4 is correct - See explanation below:

    TRK 0433
    0 is correct - My formula was not set to exact match by accident - it was counting TRK 433 - I have since corrected it.

    I obviously need help. But my issue still remains to have the count start over once the TRK leaves the yard.
    Example:
    Asset is on the yard mon, tues, and wed. Not on thurs, but comes back to yard fri and sat.
    I need to not show that asset as sitting 4+ days for the week because it is only current - 2 days on the lot.
    I need to also add the counts from the previous weeks if the asset sits week to week.

    Please help- thank you
    Attached Files Attached Files

  19. #19
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    OK, given your response above, my post #13 formula agrees with 21 out of 22 of your expected results.

    Let's focus on the one remaining discrepancy, TRK 9533 which is on-yard for 5 days, off yard for 1 day then on-yard for 4 days.

    You comment above:
    "Asset is on the yard mon, tues, and wed. Not on thurs, but comes back to yard fri and sat. I need to not show that asset as sitting 4+ days for the week because it is only current - 2 days on the lot."
    Agreed - that's why TRK 86348 counts as 6 on-yard days and not 8, but this comment does not address the TRK 9533 case where there are 5 consecutive on-yard days, then 1 off-yard day, then 4 consecutive on-yard days. Why does this not count as 5 + 4 = 9?

    You say
    "I need to also add the counts from the previous weeks if the asset sits week to week."
    Isn't this consistent with TRK 9533 counting as 5 + 4 = 9 because both of the on-yard periods are greater than 4 days?

  20. #20
    Registered User
    Join Date
    08-06-2020
    Location
    edmond, oklahoma
    MS-Off Ver
    windows 10
    Posts
    14

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    I have attached a new spreadsheet - I have added more columns for more reference/help. Note: I have given 10 days for a "week" reference- However a true week is only 7 days. When I first shared this data... I was only trying to give more data for reference.

    The Date chosen for the report is part of the criteria. I need the formula to count backwards from the date chosen and not count any other days it may have been on the yard prior to an exit of the yard that week. - You will see my filters on the columns starting on the last day of the week counting back 4.
    The reason for this report is to notify the correct person that the asset is sitting too long. If it has left the yard on the date chosen for report then their would be no reason to notify anyone-even if it sat longer than 4 days. Still sitting on the yard is the a huge part of the criteria.
    Only count consecutive days in a row.
    Second request which is an additional request: "I need to also add the counts from the previous weeks if the asset sits week to week".
    I have a new example on the spreadsheet I attached. - you will see how some assets sat all week and I added the days in the last week to the total. But only if It never left the yard.
    Again using the date as criteria to count backwards and only adding consecutive days in a row.

    Please let me know if this helps?
    Attached Files Attached Files

  21. #21
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,936

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    Try the following:
    In L5 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In W5 and down:
    Formula: copy to clipboard
    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.

  22. #22
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    You beat me to it JeteMc

    I'll post this anyway as I think (?) the OP wants to be able to specify arbitrary date ranges to analyse.

    ----------------

    OK, given your latest workbook, I *think* I understand what you are looking for. I match all but one of your expected results for both of your time intervals with the exception of truck 7455 for range 2-Jan thru 21-Jan where you say 9, but I say 8. Looking at the data I believe you have miscounted in this one instance.

    I have created a second worksheet "GWoutput" that summarizes for each truck (in col-A) the days on-yard as counted using your rules as best I understand them (in col-B). To use this worksheet:
    Enter a date in GWoutput!G1 to specify the "as-of" date of the report
    Enter a date in GWoutput!I1 to specify the date to look back to.
    Review the on-yard counts for the specified time period in col-B.

    janae1.png

    Notes:

    I HAVE HAD TO DELETE SOME WORKSHEETS UNUSED FOR THE PURPOSES OF THIS THREAD AND REDUCE MY HELPER COLUMNS FROM 1-JAN-20 THRU 12-DEC-20 TO 1-JAN-20 THRU 30-APR-20 IN ORDER TO MEET FORUM UPLOAD SIZE LIMITS.

    I have isolated my helper columns to their own worksheet which I have called "GWhelp". I have populated formulas for all of 2020 and allowed for up to 600 assets (you currently have 581). Within these limits, you can refresh the "Data" worksheet and change the date limits you are looking at without any need to look at or update the helper worksheet.

    Columns C and D in the GWoutput allow verification of my results against your expected results. To do this, set cell I1 to 2-Jan-20 and cell J1 to either 11-Jan or 21-Jan. Column-C will look up the appropriate expected results from your "Need" worksheet. Column-D will say "discrepancy" if my results differ from your expected results.

    To facilitate verification I have used conditional formatting on the GWhelp worksheet to highlight the date range of interest.

    If consecutive on-yard days are < 4 what do you want as an expected value? 1, 2 or 3 or do you expect a zero for each of these cases - I can do either way. For now I have assumed zero - the GWoutput!B:B formula would simplify considerably if you want to see 1,2 or 3 instead of zeros.

    The on-yard days formula in GWOutput!B2:B600 is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The formula creates a range from the "Back-thru" date to the "As-of" date on the GWhelp worksheet for the truck in question. The "match() then simply looks for the first instance of "0" (off-yard).
    A special case needs to be made if there are no off-yard days in the range as the match() will fail in this case.
    The repetition of most of the formula is if results of 1,2 or 3 are to be displayed as zero.

    Note that the GWhelp worksheet gets its information solely from the DATA worksheet.

    To minimize confusion GWhelp uses your convention of "1" meaning on-yard.
    The GWhelp columns are in reverse chronological order. I found this to be necessary for the on-yard days formula.

    An update to your workbook that includes the above updates is attached.
    Last edited by GeoffW283; 08-15-2020 at 09:47 PM.

  23. #23
    Registered User
    Join Date
    08-06-2020
    Location
    edmond, oklahoma
    MS-Off Ver
    windows 10
    Posts
    14

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    GeoffW283 - Yes! This is exactly what I need. Thank you! Since I will need to create a new column for each day. Plus an additional column for each week. Is there a VBA that could be written to make this faster as I will have to have this for the entire year of 2020.

    Thank you again!

  24. #24
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    I'm glad I finally managed to understand your requirement

    I'm not sure why you would need a separate weekly column.

    I actually had created a years worth of data (all of 2020) during the development of my response, but I had to remove 8 months to reduce the file size in order to meet the forum's upload size constraints. On my somewhat long in the tooth laptop the performance was reasonable.

    If you still want to investigate a VBA solution then I would create a new thread in the VBA section of the forum where you will find people with much more expertise than I could bring to bear.

    Geoff

  25. #25
    Registered User
    Join Date
    08-06-2020
    Location
    edmond, oklahoma
    MS-Off Ver
    windows 10
    Posts
    14

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    JeteMC,

    I can see now after testing the formulas you and Geoff gave me. You(JeteMC) gave me the ability to add the cells together from week to week with your second formula. Which I have to have. However in order for me to get the date ranges I'm searching for I will need the second formula you provided to jump week to week. 1/7 to 1/14 to 1/21 to 1/28 and so on. I've tried dragging it and even adding the days in between but I was not successful to get the weeks and days consecutively together added correctly through larger data.

  26. #26
    Registered User
    Join Date
    08-06-2020
    Location
    edmond, oklahoma
    MS-Off Ver
    windows 10
    Posts
    14

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    GeoffW283,

    Sorry I found after testing the formulas in my larger data that I am still missing the ability to add the days together week to week it still sits on the lot. You will see in the additional data on the last spreadsheet that I added a second 10 day week that added the totals of the last week into the totals of the next week unless it left the yard. JeteMC had a formula in his that added them together. So both of you were able to almost help me resolve it. I'm still missing the ability to show each week how long an asset has been sitting from adding them together. I have asked JeteMC to help me with the final changes for my larger data with his 2nd formula that adds the weeks together.

  27. #27
    Registered User
    Join Date
    08-06-2020
    Location
    edmond, oklahoma
    MS-Off Ver
    windows 10
    Posts
    14

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    JeteMC, and GeoffW283,

    Please disregard my last two messages. I was able to get GeoffW283s to work. Sorry. Thank you both again!

  28. #28
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    I worked this up before I saw your last post. Maybe I should quit while ahead - but I'll post anyway in case there is something useful for you.

    And thanks for the added reputation points.

    I have made a small modification to the GWoutput worksheet. It now looks like this:

    janae2.png

    The GWoutput worksheet now has multiple columns, each with their own "as-of" date and "back through" date. You can do weekly reports (of either the 7 day or 10 day variety), quarterly results - whatever reports you like. Columns B and C are the two time intervals for which you have provided expected results in your "Need" worksheet. I have added, for the purpose of example, 2 more weekly reports in columns D and E. You can add as many more reports as you like simply by copying The B4 formula across and down as far as you like and the setting the "as of" and "Back thru" dates. That's all there is to it - no need at all to mess with the "GWhelp" worksheet.

    I have retained the verification test for columns A and B against your expected results in columns L through O. These can be deleted if desired.

    Updated workbook attached.

  29. #29
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,936

    Re: Countif formula with criteria, stop counting if not found, but start count over after

    RE: Post #27
    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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] Stop Countif from counting blank cells
    By acmac1962 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-27-2019, 03:52 AM
  2. [SOLVED] Trouble with COUNTIF formula on conditional count - I want to avoid counting text.
    By Elijah in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-07-2019, 08:16 AM
  3. Counting amount of present people from start/stop times (workshifts)
    By khalman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-26-2017, 06:07 PM
  4. Count Until empty cell (condition to stop counting)
    By danielpp in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-30-2016, 02:50 PM
  5. [SOLVED] Formula for finding Start, Stop, Highest and Lowest value
    By mariur89 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-05-2016, 06:07 AM
  6. formula start and stop counting
    By MM80 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-18-2016, 05:37 AM
  7. [SOLVED] Struggling to build count/countif/countifs formula with various comparative criteria!
    By dropanddrive03 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-16-2014, 03:04 AM

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