Need formula for this logic

[sathyaanil37]

Hi,

We have hourly data in excel like below. i need logic where sum of values for that day is greater than 19.
that means here sum of value for this date 3/29/2012 is 45.and for3/30/2012 is 10. so here i need one output column that is saying Count of days=1 based on date.here 3/29/2012 is 45 and 3/30/2012 is 10.so 45>19 then count of days =1

Input format

Name datetime Value
Test 3/29/2012 1:19 0
Test 3/29/2012 2:19 20
Test 3/29/2012 3:19 25
Test 3/29/2012 4:19 0
Test 3/29/2012 5:19 0
Test 3/29/2012 6:19 0
Test 3/29/2012 7:19 0
Test 3/29/2012 8:19 0
Test 3/29/2012 9:19 0
Test 3/29/2012 10:19 0
Test 3/29/2012 11:19 0
Test 3/29/2012 12:19 0
Test 3/29/2012 13:19 0
Test 3/29/2012 14:19 0
Test 3/29/2012 15:19 0
Test 3/29/2012 16:19 0
Test 3/29/2012 17:19 0
Test 3/29/2012 18:19 0
Test 3/29/2012 19:19 0
Test 3/29/2012 20:19 0
Test 3/29/2012 21:19 0
Test 3/29/2012 22:19 0
Test 3/29/2012 23:19 0
Test 3/30/2012 16:19 0
Test 3/30/2012 17:19 0
Test 3/30/2012 18:19 0
Test 3/30/2012 19:19 0
Test 3/30/2012 20:19 10
Test 3/30/2012 21:19 0
Test 3/30/2012 22:19 0
Test 3/30/2012 23:19 0

Output format

Name datetime Value Countofdays
Test 03/29/12 01:19 AM 0 1
Test 03/29/12 02:19 AM 20
Test 03/29/12 03:19 AM 25
Test 03/29/12 04:19 AM 0
Test 03/29/12 05:19 AM 0
Test 03/29/12 06:19 AM 0
Test 03/29/12 07:19 AM 0
Test 03/29/12 08:19 AM 0
Test 03/29/12 09:19 AM 0
Test 03/29/12 10:19 AM 0
Test 03/29/12 11:19 AM 0
Test 03/29/12 12:19 PM 0
Test 03/29/12 01:19 PM 0
Test 03/29/12 02:19 PM 0
Test 03/29/12 03:19 PM 0
Test 03/29/12 04:19 PM 0
Test 03/29/12 05:19 PM 0
Test 03/29/12 06:19 PM 0
Test 03/29/12 07:19 PM 0
Test 03/29/12 08:19 PM 0
Test 03/29/12 09:19 PM 0
Test 03/29/12 10:19 PM 0
Test 03/29/12 11:19 PM 0
Test 03/30/12 04:19 PM 0
Test 03/30/12 05:19 PM 0
Test 03/30/12 06:19 PM 0
Test 03/30/12 07:19 PM 0
Test 03/30/12 08:19 PM 10
Test 03/30/12 09:19 PM 0
Test 03/30/12 10:19 PM 0
Test 03/30/12 11:19 PM 0
Edit Post Reply

[Ace_XL]

Use..in Column E

=IF(COUNTIF($B$2:B2,B2)>1,"",IF(AND(SUMIF(B:B,B2,D:D)>SUMIF(B:B,B2+1,D:D),SUMIF(B:B,B2+1,D:D)>0),1,""))

[jeffreybrown]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[arlu1201]

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.