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

[Janae]

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.

[FDibbins]

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.

[FDibbins]

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

[Janae]

"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.

[oeldere]

"and some manually calculated results"

[Janae]

What is the solution Oeldere?

[oeldere]

Solution removed since the pivot table is not correct.

Edit

post my solution in #11.

[Janae]

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

[Janae]

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

[FDibbins]

Follow the directions in the yellow banner at the top.

Also, I gave directions to you in post #2

[oeldere]

See the attached file (pivot table).

See if this fits your needs.

[Janae]

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.

[GeoffW283]

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:

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:

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.

[oeldere]

@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.

[Janae]

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.

[Janae]

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

[GeoffW283]

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?

[Janae]

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

[GeoffW283]

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?

[Janae]

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?

[JeteMc]

Try the following:
In L5 and down:

Please Login or Register  to view this content.

In W5 and down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[GeoffW283]

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:

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.

[Janae]

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!

[GeoffW283]

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

[Janae]

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.

[Janae]

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.

[Janae]

JeteMC, and GeoffW283,

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

[GeoffW283]

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.

[JeteMc]

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.