Equation got to complicated for me

[dmheller]

All,
Here is where I started.
=1440-SUM(IF(ISNUMBER('Line 3'!$D$4:$D$44903),IF(INT('Line 3'!$D$4:$D$44903)=INT('Line 3 calc Sheet'!B18),--(('Line 3 data copy'!$E$4:$E$44703>220)*ISNUMBER('Line 3 data copy'!$E$4:$E$44703)+('Line 3 data copy'!$F$4:$F$44703>220)*ISNUMBER('Line 3 data copy'!$F$4:$F$44703)+ ('Line 3 data copy'!$G$4:$G$44703>220)*ISNUMBER('Line 3 data copy'!$G$4:$G$44703)+('Line 3 data copy'!$H$4:$H$44703>220)*ISNUMBER('Line 3 data copy'!$H$4:$H$44703)>0))))
Now this isn't to bad as it looks up a date then finds the date and sums it in the date. So if any of E, F, G, or H are above 220 in that date, it sums that total and that's what I wont this equation to do and it works. Now I want to find how many times in that day the or state me changes but only changes from >220 to <220. I do not care if it goes from <220 to over 220. I don't know if that makes any since but I might have to throw in a dummy column to make this work. Let me know if you have any ideas.
thanks

 

[oldbrewer]

can we see some raw (pretend) data please ?

 

[dmheller]

Here would be an example, at 8:26 it goes from being over 220 to less than 220.
[TABLE="width: 389"]
<tbody>[TR]
[TD]10/16/17 8:17:00 AM[/TD]
[TD]233.9905[/TD]
[TD]238.012[/TD]
[TD]243.9955[/TD]
[TD]249.9958[/TD]
[/TR]
[TR]
[TD]10/16/17 8:18:00 AM[/TD]
[TD]233.9905[/TD]
[TD]238.012[/TD]
[TD]243.9955[/TD]
[TD]249.9958[/TD]
[/TR]
[TR]
[TD]10/16/17 8:19:00 AM[/TD]
[TD]233.9905[/TD]
[TD]238.012[/TD]
[TD]243.9955[/TD]
[TD]249.9958[/TD]
[/TR]
[TR]
[TD]10/16/17 8:20:00 AM[/TD]
[TD]233.9905[/TD]
[TD]238.012[/TD]
[TD]243.9955[/TD]
[TD]249.9958[/TD]
[/TR]
[TR]
[TD]10/16/17 8:21:00 AM[/TD]
[TD]233.9905[/TD]
[TD]238.012[/TD]
[TD]243.9955[/TD]
[TD]249.9958[/TD]
[/TR]
[TR]
[TD]10/16/17 8:22:00 AM[/TD]
[TD]233.9905[/TD]
[TD]238.012[/TD]
[TD]243.9955[/TD]
[TD]249.9958[/TD]
[/TR]
[TR]
[TD]10/16/17 8:23:00 AM[/TD]
[TD]233.9905[/TD]
[TD]238.012[/TD]
[TD]243.9955[/TD]
[TD]249.9958[/TD]
[/TR]
[TR]
[TD]10/16/17 8:24:00 AM[/TD]
[TD]233.9905[/TD]
[TD]237.9968[/TD]
[TD]243.9955[/TD]
[TD]249.9958[/TD]
[/TR]
[TR]
[TD]10/16/17 8:25:00 AM[/TD]
[TD]233.9905[/TD]
[TD]237.9968[/TD]
[TD]243.9955[/TD]
[TD]249.9958[/TD]
[/TR]
[TR]
[TD]10/16/17 8:26:00 AM[/TD]
[TD]182.8891[/TD]
[TD]184.1762[/TD]
[TD]200.7086[/TD]
[TD]194.502[/TD]
[/TR]
[TR]
[TD]10/16/17 8:27:00 AM[/TD]
[TD]142.2131[/TD]
[TD]155.6269[/TD]
[TD]167.3712[/TD]
[TD]164.9063[/TD]
[/TR]
[TR]
[TD]10/16/17 8:28:00 AM[/TD]
[TD]125.5814[/TD]
[TD]137.6054[/TD]
[TD]150.6807[/TD]
[TD]148.9837[/TD]
[/TR]
[TR]
[TD]10/16/17 8:29:00 AM[/TD]
[TD]112.5205[/TD]
[TD]124.2124[/TD]
[TD]139.1186[/TD]
[TD]136.6748[/TD]
[/TR]
[TR]
[TD]10/16/17 8:30:00 AM[/TD]
[TD]102.2394[/TD]
[TD]113.7578[/TD]
[TD]129.8345[/TD]
[TD]126.7011[/TD]
[/TR]
[TR]
[TD]10/16/17 8:31:00 AM[/TD]
[TD]94.2112[/TD]
[TD]104.719[/TD]
[TD]121.7752[/TD]
[TD]118.3883[/TD]
[/TR]
[TR]
[TD]10/16/17 8:32:00 AM[/TD]
[TD]87.4529[/TD]
[TD]97.67177[/TD]
[TD]114.8455[/TD]
[TD]110.6334[/TD]
[/TR]
[TR]
[TD]10/16/17 8:33:00 AM[/TD]
[TD]81.58927[/TD]
[TD]91.53004[/TD]
[TD]108.5879[/TD]
[TD]103.9354[/TD]
[/TR]
[TR]
[TD]10/16/17 8:34:00 AM[/TD]
[TD]76.72224[/TD]
[TD]86.50076[/TD]
[TD]102.956[/TD]
[TD]97.81688[/TD]
[/TR]
[TR]
[TD]10/16/17 8:35:00 AM[/TD]
[TD]72.4254[/TD]
[TD]81.88866[/TD]
[TD]97.53431[/TD]
[TD]92.57902[/TD]
[/TR]
</tbody><colgroup><col><col span="4"></colgroup>[/TABLE]

 

[oldbrewer]

so if any one of the 4 columns goes from > 220 to < 220 OR < 220 to > 220 then you want to add all 4 numbers and do the same for any date_time where this happens ?

 

[dmheller]

No,
So currently, the way the equation works all 4 columns have to fall below 220 and this is also one min data. So with that said, I want any time all columns fall below 220 after the previous min were one was above 220. This I want to sum the number of times that happens in a given day.

 

[oldbrewer]

I think I understand now. Detect when all 4 columns fall below 220 for the first time. Then detect when all 4 columns rise above 220........and so on. Count the number of occurrencies per day.

 

[oldbrewer]

[TABLE="width: 1075"]
<colgroup><col><col span="4"><col><col span="10"></colgroup><tbody>[TR]
[TD]date_time[/TD]
[TD]param1[/TD]
[TD]param2[/TD]
[TD]param3[/TD]
[TD]param4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/10/2017 01:00[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/10/2017 03:00[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]NNNN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/10/2017 05:00[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]U[/TD]
[TD]UNNU[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/10/2017 07:00[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]U[/TD]
[TD]N[/TD]
[TD]NNUN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/10/2017 09:00[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]DDDD[/TD]
[TD]DOWN[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/10/2017 11:00[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]N[/TD]
[TD]NUUN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/10/2017 13:00[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD]N[/TD]
[TD]D[/TD]
[TD]U[/TD]
[TD]UNDU[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/10/2017 15:00[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]NDNN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/10/2017 17:00[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]U[/TD]
[TD]N[/TD]
[TD]NNUN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/10/2017 19:00[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]DNDD[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/10/2017 21:00[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]UUUU[/TD]
[TD]UP[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/10/2017 23:00[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]DDDN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/10/2017 01:00[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]N[/TD]
[TD]UUUN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/10/2017 03:00[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]DDDD[/TD]
[TD]DOWN[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/10/2017 05:00[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD]N[/TD]
[TD]U[/TD]
[TD]N[/TD]
[TD]UNUN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/10/2017 07:00[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]DNDN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/10/2017 09:00[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]UUUU[/TD]
[TD]UP[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/10/2017 11:00[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]DDDD[/TD]
[TD]DOWN[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/10/2017 13:00[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]N[/TD]
[TD]U[/TD]
[TD]UUNU[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/10/2017 15:00[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]NDNN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/10/2017 17:00[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]N[/TD]
[TD]NUUN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/10/2017 19:00[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]DDDD[/TD]
[TD]DOWN[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/10/2017 21:00[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]UUNN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/10/2017 23:00[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]DDNN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/10/2017 01:00[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]UUUU[/TD]
[TD]UP[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/10/2017 03:00[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]DDDN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/10/2017 05:00[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]U[/TD]
[TD]D[/TD]
[TD]NNUD[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/10/2017 07:00[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]NNDN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/10/2017 09:00[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]UUUU[/TD]
[TD]UP[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/10/2017 11:00[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]DNDN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/10/2017 13:00[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD]N[/TD]
[TD]U[/TD]
[TD]N[/TD]
[TD]UNUN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/10/2017 15:00[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]DDDD[/TD]
[TD]DOWN[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/10/2017 17:00[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]UUUU[/TD]
[TD]UP[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/10/2017 19:00[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]DDDD[/TD]
[TD]DOWN[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/10/2017 21:00[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]NUUU[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/10/2017 23:00[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]NNNN[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]UP[/TD]
[TD]DOWN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01/10/2017[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="colspan: 5"]I think the best way is to use some helper columns[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]02/10/2017[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD="colspan: 5"]to find out whether each column is up or down[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]03/10/2017[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD="colspan: 3"]then concatenate the outcome[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]and detect instances of UUUU and DDDD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]05/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]06/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]an easy sumproduct table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]does the daily analysis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[dmheller]

Thank you, that is the way I went, I had to add helper columns. Then just wroth the logic into the column then I did a sumif for the dates based on the helper columns. Thanks for your help and glad to see I wasn't going insane over something I couldn't do.