SumIfs on Two Dates

G

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
946
Office Version
  1. 2016
Platform
  1. Windows
Hello All
I have the following layout

1643212904766.png


I'm having trouble with my SUMIFS.
If the cell in column W is blank, then the numbers in column X would be added in column Y, based upon the two dates of 1-3-2022 & 1-4-2022.
then the SUMIFS would continue with the next set of two dates of 1-4-2022 & 1-5-2022....and so on to the bottom of the data set.
So in this example, cellY3 would equal 1957.61 based upon the first date of 1-3-2022.
Is this possible?
Thank you

The formula I am using is:
VBA Code: 
=SUMIFS(X:X,T:T,"",AA:AA,AND(AA3,AA4))
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
This is untested since I can't copy your data, but try this in YY3:

Excel Formula: 
=SUMIFS(X:X,T:T,AA3,W:W,"")+SUMIFS(X:X,T:T,AA4,W:W,"")
 
Upvote 0
Eric W said:
This is untested since I can't copy your data, but try this in YY3:

Excel Formula: 
=SUMIFS(X:X,T:T,AA3,W:W,"")+SUMIFS(X:X,T:T,AA4,W:W,"")
Click to expand...
So this does work sort of. The problem is it also picks up 1-4-2022 in the 1-4-2022 and 1-5-2022 segment.
Basically, I have a 2nd shift that begins at 05:00PM and ends at 04:00AM.
So technically, the shift begins on the 3rd and extends into the 4th. the next day, 2nd shift begins on the 4th and ends on the 5th, the next day 2nd shift begins on the 5 and ends on the 6th...and so on.
I can get numbers segmented into the shift time frame with:
VBA Code: 
=IF(OR($Q$2=$U510,$R$2=$U510),$V510, IF(MEDIAN($Q$2,$R$2,$U510)=$U510,IF($Q$2<$R$2,$V510,""),IF($R$2<$Q$2,$V510,"")))
This code with the MEDIAN function is working very good (thank you BSALV).
Below, you see a 2nd shift Segment that captures data at 7:50pm and ends data capture at 2:51:55 am the next day crossing midnight. So this would be a 2nd shift that starts on 1-4-2022 and column X would be summed. because it falls into the correct time frame beginning on the 4th. Because Column W is blank, I'm trying to add just Column X because this would fall into the time slot of start/finish beginning on 1-4-2022 and moving across midnight into 1-5-2022.

Thank you for your help

1643222675082.png
 
Upvote 0
I have a test excel sheet, but am not seeing how to attach the excel sheet here?
 
Upvote 0
I have a test excel sheet, but am not seeing how to attach the excel sheet here?
Click to expand...

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
OK, for second shift how about:

Excel Formula: 
=SUMIFS(X:X,T:T,AA3,W:W,"",U:U,">="&$Q$2)+SUMIFS(X:X,T:T,AA4,W:W,"",U:U,"<="&$R$2)

First shift would look like:

Excel Formula: 
=SUMIFS(X:X,T:T,AA3,W:W,"",U:U,">="&$O$2,U:U,"<="&$P$2)
 
Upvote 0
Solution
New Microsoft Excel Worksheet (2).xlsx
OPQRSTUVWXYZAA
11st Shift2nd Shift1st Shift2nd shiftDayDate
25:00:0016:00:005:00:00 PM4:00:00 AMDATETIMEACTUALActual Wt.Actual Wt.Sun1/2/2022
31/3/20225:14:53 AM64.6064.60 5796.06Mon1/3/2022
4Only Paste DATE, TIME, ACTUAL (paste values only)1/3/20225:15:21 AM5.715.71 9850.26Tue1/4/2022
51/3/20225:16:29 AM18.2518.25 Wed1/5/2022
61/3/20225:17:35 AM17.7317.73 Thu1/6/2022
71/3/20225:18:45 AM18.5618.56 Fri1/7/2022
81/3/20225:19:59 AM16.4816.48 Sat1/8/2022
91/3/20225:21:01 AM22.2322.23 Sun1/9/2022
101/3/20225:22:51 AM17.8117.81 Mon1/10/2022
111/3/20225:23:50 AM13.1913.19 Tue1/11/2022
121/3/20225:25:34 AM15.0515.05 Wed1/12/2022
131/3/20222:08:14 PM13.3613.36 Thu7/21/2022
141/3/20222:08:37 PM0.000.00 Fri7/22/2022
151/3/20222:09:20 PM13.6413.64 Sat7/23/2022
161/3/20222:17:01 PM31.7631.76 Sun7/24/2022
171/3/20222:20:45 PM18.8118.81 Mon7/25/2022
181/3/20222:22:50 PM25.5425.54 Tue7/26/2022
191/3/20222:24:37 PM13.7113.71 Wed7/27/2022
201/3/20222:39:38 PM45.5645.56 Thu7/28/2022
211/3/20222:40:53 PM13.1213.12 Fri7/29/2022
221/3/20222:46:05 PM57.5357.53 Sat7/30/2022
231/3/20222:48:41 PM23.6323.63 Sun7/31/2022
241/3/20222:49:32 PM14.0114.01 Mon8/1/2022
251/3/20222:58:34 PM85.0885.08 Tue8/2/2022
261/3/20223:05:39 PM48.8448.84 Wed8/3/2022
271/3/20223:07:32 PM23.7623.76 Thu8/4/2022
281/3/20223:08:36 PM14.4414.44 Fri8/5/2022
291/3/20223:09:54 PM15.3115.31 Sat8/6/2022
301/3/20225:24:09 PM37.95 37.95Sun8/7/2022
311/3/20225:25:43 PM19.83 19.83Mon8/8/2022
321/3/20225:26:31 PM0.55 0.55Tue8/9/2022
331/3/20225:37:58 PM160.69 160.69Wed8/10/2022
341/3/20225:42:17 PM63.12 63.12Thu8/11/2022
351/3/20225:43:38 PM14.74 14.74Fri8/12/2022
361/3/20225:47:59 PM62.11 62.11Sat8/13/2022
371/3/20225:55:49 PM85.07 85.07Sun8/14/2022
381/3/20228:34:40 PM22.23 22.23Sun9/4/2022
391/3/20228:35:59 PM20.69 20.69Mon9/5/2022
401/3/20228:42:41 PM21.63 21.63Tue9/6/2022
411/3/20228:44:13 PM20.83 20.83Wed9/7/2022
421/3/20228:47:43 PM45.26 45.26Thu9/8/2022
431/3/20229:23:53 PM24.44 24.44Fri9/9/2022
441/3/20229:25:26 PM23.00 23.00Sat9/10/2022
451/3/20229:28:49 PM33.92 33.92Sun9/11/2022
461/3/20229:30:02 PM19.52 19.52Mon9/12/2022
471/3/20229:31:11 PM18.81 18.81Tue9/13/2022
481/3/20229:32:20 PM19.35 19.35Wed9/14/2022
491/4/20221:47:34 AM65.16 65.16Fri10/14/2022
501/4/20221:55:19 AM54.05 54.05Sat10/15/2022
511/4/20222:01:34 AM32.81 32.81Sun10/16/2022
521/4/20222:07:23 AM65.10 65.10Mon10/17/2022
531/4/20222:12:29 AM40.05 40.05Tue10/18/2022
541/4/20222:17:49 AM44.17 44.17Wed10/19/2022
551/4/20222:20:26 AM21.41 21.41Thu10/20/2022
561/4/20222:22:55 AM40.48 40.48Fri10/21/2022
571/4/20222:33:35 AM126.53 126.53Sat10/22/2022
581/4/20222:40:32 AM97.44 97.44Sun10/23/2022
591/4/20222:44:06 AM41.05 41.05Mon10/24/2022
601/4/20222:49:55 AM81.94 81.94Tue10/25/2022
611/4/20222:51:23 AM21.64 21.64Wed10/26/2022
621/4/20222:52:36 AM20.83 20.83Thu10/27/2022
631/4/20222:56:17 AM55.00 55.00Fri10/28/2022
641/4/20223:01:56 AM85.26 85.26Sat10/29/2022
651/4/20225:13:16 AM5.545.54 Sun10/30/2022
661/4/20225:14:40 AM11.6711.67 Mon10/31/2022
671/4/20225:19:36 AM22.5122.51 Tue11/1/2022
681/4/20225:20:35 AM14.7014.70 Wed11/2/2022
691/4/20225:26:53 AM14.1814.18 Thu11/3/2022
701/4/20225:30:15 AM14.1914.19 Fri11/4/2022
711/4/20225:31:35 AM15.3715.37 Sat11/5/2022
Sheet1
Cell Formulas
RangeFormula
W3:W71W3=IF(AND($U3>=$O$2,$U3<=$P$2),$V3,"")
X3:X71X3=IF(OR($Q$2=$U3,$R$2=$U3),$V3, IF(MEDIAN($Q$2,$R$2,$U3)=$U3,IF($Q$2<$R$2,$V3,""),IF($R$2<$Q$2,$V3,"")))
Y3:Y4Y3=SUMIFS(X:X,T:T,AA3,W:W,"")+SUMIFS(X:X,T:T,AA4,W:W,"")
Z2:Z71Z2=IF(AA2<>"",TEXT($AA2,"ddd"),"")


Not sure if this is how minisheets works...I deleted a lot of rows to get it here
Thanks
 
Upvote 0
Eric W said:
OK, for second shift how about:

Excel Formula: 
=SUMIFS(X:X,T:T,AA3,W:W,"",U:U,">="&$Q$2)+SUMIFS(X:X,T:T,AA4,W:W,"",U:U,"<="&$R$2)

First shift would look like:

Excel Formula: 
=SUMIFS(X:X,T:T,AA3,W:W,"",U:U,">="&$O$2,U:U,"<="&$P$2)
Click to expand...
WOW...2nd shift looks like it is working...that crossing over midnight as been messing with me for a very long time. Thank you!

The 1st shift I must have copied wrong as it is not working for me.
Fortunately, 1st shift remains on the same day, so it is much easier.
I'm using:
VBA Code: 
=SUMIFS($W:$W,$T:$T,"="&$AA3)

which seems to be working for 1st shift.
Thank you again for the help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
SUMIFS if a date is within the range
Replies
1
Views
180
Fluff
Fluff
S
  • Question
Date formatting issue sheet to VBA Array to Sheet
Replies
2
Views
147
MARK858
MARK858
H
  • Question
Avoiding INDIRECT when referencing and comparing ranges in different workbooks
Replies
1
Views
270
RoryA
RoryA
SaraWitch
  • Solved
Extract individual lines from cell
Replies
6
Views
237
SaraWitch
SaraWitch
G
  • Solved
Parse dates into comma delimit values
Replies
3
Views
335
DanteAmor
DanteAmor

Forum statistics

Threads
1,214,827
Messages
6,121,823
Members
449,049
Latest member
cybersurfer5000

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top