countif or sumproduct with 2 part formula.

Ok, I know I have to be missing the simplest thing, but I have been trying to fix this since yesterday. I have googled but can not quite find where I am messing up this formula. I have attached the sheet. I am trying to count the number of late unloads, but only if they are red. Reasoning is if the truck is late the unload being late doesnt matter which is why I have it set in conditional formatting to ignore those. based on the sheet you will see some of the trucks arrive early, but can still be late unload... this is the formula I am using currently. I have edited it so many times I do not remember how I had it. But I had it working right before, just not when a truck would show up early. Any help would be great. TIA

=SUMPRODUCT((((E2:E800-C2:C800)>0.084)*1)-(D2:D800-C2:C800>0))

Also it seems to work properly as long as the truck wasnt early..

Can a mod rename this? I think I might have not titled it properly.

Originally Posted by FDibbins

What would you change the title to?

Honestly I don't know. I think it would be something more along the lines of counting conditional formatting if another condition doesnt exist?

One possible solution is to use your CF formula in a helper column (with 1 /0 results) and the then SUM the results.

Originally Posted by JohnTopley

One possible solution is to use your CF formula in a helper column (with 1 /0 results) and the then SUM the results.

I was trying to do that.. I couldnt get it to work. The problem I am seeing is the formula does as I say but if I have 6 late trucks and 5 late unloads, 4 of which were from late trucks I end up with -1 for late unloads when I should have 1.

Also How do I make it output 1 or 0?

The sheet is attached I think. WHat I have is 2 conditional formats. Late truck is simply d1>c1 then late unload is =((E1-C1)>0.084)>(D1>C1) when a truck is late the late unload no longer applies, but I need to keep count of how many real late unloads there are. I am so close I can taste it but I cant get any formulas right. I basisically only need to count the red from CF in column e

Originally Posted by jnepsa

The sheet is attached I think. WHat I have is 2 conditional formats. Late truck is simply d1>c1 then late unload is =((E1-C1)>0.084)>(D1>C1) when a truck is late the late unload no longer applies, but I need to keep count of how many real late unloads there are. I am so close I can taste it but I cant get any formulas right. I basisically only need to count the red from CF in column e

I should add, everything is based of C the scheduled time. If the truck is a day early, but unloaded at 2hr 1min late its our cost. If the truck is 1min late the unload no longer matters.

Originally Posted by FDibbins

If you only want Unload vs Sched, then this gives 12...
=SUMPRODUCT(--(E2:E14>C2:C14))

correct, but I only want to count late unloads which is where the >.084 comes in to give me that. But if the truck is late it doesnt count
In the attached sheet I should have a 2 under late unloads. I was thinking =if(countif but I cant seem to get the freaking formula right. Too many or not enough aruments.

Originally Posted by FlameRetired

If I am interpreting correctly try this.

=SUMPRODUCT((((E2:E800-C2:C800)>0.084)*1)-(D2:D800<C2:C800))

Tried it, and just tried it again to be sure.. It gives me one because there are technically only 1 more late unload than late trucks.

Originally Posted by FDibbins

Indicate which entries you want counted please?
guessing...
=SUMPRODUCT((((E2:E800-C2:C800)>0.084)*1)-(e2:e800<d2:d800))

Column E Late Unload only in red. My conditional formatting elimates the red if the truck was late and is yellow. Box I4 should say 2 right now with the two red late unloads. When column d is yellow the time in column e no longer matters

For the record I truly appreciate the help. the problem with the equations are that an early truck, or more late trucks than unloads kicks it out. I almost need it to be count e2:e800-c2:c800>.084 only if d2:d800<c2:c800 or dont count if greater

Originally Posted by avk

explain about criteria between Scheduled Date/Time & Unload Date/Time
also Arrival Date/Time & Unload Date/Time.

I have notice that (refer yellow highlighted row 5) Hrs Calculate between Unload - Scheduled is : 33
and Hrs Calculate between Unload - Arrival is 8.50
In this case unload time is less than arrival time how is possible?

Every thing is based off Column C "Scheduled Date/Time" If a truck shows up at even 1 minute late we are not responsible for when they get unloaded, they get worked in when we have time. If a truck shows up early to its scheduled time, we still must unload it with in 2 hours 1 min of its scheduled time. We only track Column D "arrival date/time" so we know when we are not responisble to pay a fee. IE truck a was schediled to arrive 2/21/17 8:00am and showed up at 2/21/17 6:00am we still must have it unloaded by 2/21/17 10:01 am or we pay a fee. Now if that same truck with the same scheduled time arrived at 2/21/17 8:30 am we are no longer responsible for a fee, even if it is days before we unload. does that help?

Originally Posted by avk

explain about criteria between Scheduled Date/Time & Unload Date/Time
also Arrival Date/Time & Unload Date/Time.

I have notice that (refer yellow highlighted row 5) Hrs Calculate between Unload - Scheduled is : 33
and Hrs Calculate between Unload - Arrival is 8.50
In this case unload time is less than arrival time how is possible?

and more specifically that truck was 24.5 hours late and we could not get to it for another 9 hours.

Try

=SUMPRODUCT((D2:D500<C2:C500)*(E2:E500-C2:C500>0.084))

result is 2!

Originally Posted by JohnTopley

Try

=SUMPRODUCT((D2:D500<C2:C500)*(E2:E500-C2:C500>0.084))

result is 2!

HOLY **//#%$&@%&#%@ Thank you.. Let me double check this is gonna work, I had it working yesterday until I got into early trucks. If this works I owe you a beer, and I will mark solved but heres some rep now!!!

Originally Posted by JohnTopley

Try

=SUMPRODUCT((D2:D500<C2:C500)*(E2:E500-C2:C500>0.084))

result is 2!

I do have a question can you explain why that worked over the previous solution? I tried similar but had them e-c first

This is your posted formula

=SUMPRODUCT((((E2:E800-C2:C800)>0.084)*1)-(D2:D800-C2:C800>0))

vs

=SUMPRODUCT((D2:D500<C2:C500)*(E2:E500-C2:C500>0.084))

The "*" is an "AND" construct so we test if (D < C) AND (E-C > 0.084)

this is "translating" your post ....

e2:e800-c2:c800>.084 only if d2:d800<c2:c800

"only if" equates to "AND"

Hope this makes sense.

SHoot I thought * was multiplication... what a dummy thank you lol made perfect sense

Just for completeness an "OR" condition uses "+".

Both work on the principle that TRUE=1, FALSE=0 so If A AND B are TRUE then we multiply (So YES it is multiply!) 1 * 1 = 1 . If either are FALSE then we have 1*0 or 0*1 =0.

For an "OR" condition we add so A OR B are TRUE we will add 1+0 or 0+1 =1: if neither are TRUE we get 0+0=0

All clear now!

Originally Posted by JohnTopley

Just for completeness an "OR" condition uses "+".

Both work on the principle that TRUE=1, FALSE=0 so If A AND B are TRUE then we multiply (So YES it is multiply!) 1 * 1 = 1 . If either are FALSE then we have 1*0 or 0*1 =0.

For an "OR" condition we add so A OR B are TRUE we will add 1+0 or 0+1 =1: if neither are TRUE we get 0+0=0

All clear now!

Much better, I love excel, but learn something new all the time.

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

Originally Posted by JohnTopley

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

Running through some data to double check, will do in about 2 min