SUMIFS not Working

T

tarunr9

New Member
Joined
Aug 13, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Iam trying to calculate the sum of QTY mentioned as per week start and week end dates , but for some reason it is not reading the qty if the dates are outside of the range. Please see the highlighted cells as an example. Can you please tell how to fix this. Thank you so much in advance

05. CPB QMR (Remeasure) 09-Nov-23.xlsb
XYZAAABACADAE
3StartFINISHQtyUnitWire&Cabl - Terminations
4325/10/20235/01/20242EAWeek StartWeek FinishRaceway
4425/10/20235/01/20242EA6/10/202313-Oct-23344
4525/10/20235/01/20241EA13/10/202320-Oct-23585
4625/10/20235/01/20241EA20/10/202327-Oct-230
4725/10/20235/01/20242EA27/10/20233-Nov-230
4825/10/20235/01/20241EA3/11/202310-Nov-230
4925/10/20235/01/20246EA10/11/202317-Nov-23105
5025/10/20235/01/20241EA17/11/202324-Nov-23223
5125/10/20235/01/20248EA24/11/20231-Dec-23148
5225/10/20235/01/20241EA1/12/20238-Dec-23227
5325/10/20235/01/202462EA8/12/202315-Dec-23264
5425/10/20235/01/20241EA15/12/202322-Dec-230
5525/10/20235/01/20243EA22/12/202329-Dec-230
5625/10/20235/01/20240EA29/12/20235-Jan-24642
5725/10/20235/01/20240EA5/01/202412-Jan-240
5825/10/20235/01/20240EA12/01/202419-Jan-24638
5925/10/20235/01/20240EA19/01/202426-Jan-2450
6025/10/202313/11/20232EA26/01/20242-Feb-24187
6125/10/202313/11/20232/02/20249-Feb-240
6225/10/202313/11/20232LM9/02/202416-Feb-2475
6325/10/202313/11/20231EA16/02/202423-Feb-242195
6425/10/202313/11/202336LM23/02/20241-Mar-24188
6525/10/202313/11/202312EA1/03/20248-Mar-2490
6625/10/202313/11/20232EA8/03/202415-Mar-24130
6725/10/202313/11/20233EA15/03/202422-Mar-24668
6825/10/202313/11/202325LM22/03/202429-Mar-24144
6925/10/202313/11/20231EA29/03/20245-Apr-2423
7025/10/202313/11/202327LM5/04/202412-Apr-240
7125/10/202313/11/20230EA12/04/202419-Apr-240
7226/10/202321/11/2023255LM19/04/202426-Apr-24282
7326/10/202321/11/202331LM26/04/20243-May-240
7426/10/202321/11/20230LM3/05/202410-May-240
7526/10/202321/11/20230LM10/05/202417-May-240
7626/10/202321/11/20230EA17/05/202424-May-240
7726/10/202321/11/20230EA24/05/202431-May-240
7826/10/202321/11/20230EA31/05/20247-Jun-240
QMR (2)
Cell Formulas
RangeFormula
AE44:AE78AE44=SUMIFS(Z:Z, X:X, ">="&AC44, Y:Y, "<="&AD44, U:U, "Raceway", AA:AA, "LM")
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
tarunr9 said:
Iam trying to calculate the sum of QTY mentioned as per week start and week end dates ,
Click to expand...
Tell us in words exactly what you mean by that and what you are trying to do.

Your formula is looking to add rows where the column X date is greater than or equal to the col AC date and the col Y date is less than or equal the col AD date.
The row 62 that you have highlighted does not meet those requirements so is not included in the sum.
 
Upvote 0
Peter_SSs said:
Tell us in words exactly what you mean by that and what you are trying to do.

Your formula is looking to add rows where the column X date is greater than or equal to the col AC date and the col Y date is less than or equal the col AD date.
The row 62 that you have highlighted does not meet those requirements so is not included in the sum.
Click to expand...
Basically I am trying to get qty number are per Week start and Week Finish dates.
Activities has start and end dates, if any part of these dates falls under Week start and Week Finish dates, it should sum up all those quantities as per Week start and Week Finish dates.
 
Upvote 0
tarunr9 said:
Activities has start and end dates, if any part of these dates falls under Week start and Week Finish dates, it should sum up all those quantities as per Week start and Week Finish dates.
Click to expand...
Then I think that you need something like this. I have not included the col U condition since you didn't give any sample data for that. In my sample I have highlighted what is added to get the value in cells AE47 (green) and AE50 (blue). Is that what you need?

23 11 15.xlsm
XYZAAABACADAE
3StartFINISHQtyUnit
4325/10/20235/01/20242EAWeek StartWeek FinishRaceway
4425/10/20235/01/20242EA6/10/202313/10/20230
4525/10/20235/01/20241EA13/10/202320/10/20230
4625/10/20235/01/20241EA20/10/202327/10/2023376
4725/10/20235/01/20242EA27/10/20233/11/2023376
4825/10/20235/01/20241EA3/11/202310/11/2023376
4925/10/20235/01/20246EA10/11/202317/11/2023376
5025/10/20235/01/20241EA17/11/202324/11/2023286
5125/10/20235/01/20248EA24/11/20231/12/20230
5225/10/20235/01/20241EA1/12/20238/12/20230
5325/10/20235/01/202462EA8/12/202315/12/20230
5425/10/20235/01/20241EA15/12/202322/12/20230
5525/10/20235/01/20243EA22/12/202329/12/20230
5625/10/20235/01/20240EA29/12/20235/01/20240
5725/10/20235/01/20240EA5/01/202412/01/20240
5825/10/20235/01/20240EA12/01/202419/01/20240
5925/10/20235/01/20240EA19/01/202426/01/20240
6025/10/202313/11/20232EA26/01/20242/02/20240
6125/10/202313/11/20232/02/20249/02/20240
6225/10/202313/11/20232LM9/02/202416/02/20240
6325/10/202313/11/20231EA16/02/202423/02/20240
6425/10/202313/11/202336LM23/02/20241/03/20240
6525/10/202313/11/202312EA1/03/20248/03/20240
6625/10/202313/11/20232EA8/03/202415/03/20240
6725/10/202313/11/20233EA15/03/202422/03/20240
6825/10/202313/11/202325LM22/03/202429/03/20240
6925/10/202313/11/20231EA29/03/20245/04/20240
7025/10/202313/11/202327LM5/04/202412/04/20240
7125/10/202313/11/20230EA12/04/202419/04/20240
7226/10/202321/11/2023255LM19/04/202426/04/20240
7326/10/202321/11/202331LM26/04/20243/05/20240
7426/10/202321/11/20230LM3/05/202410/05/20240
7526/10/202321/11/20230LM10/05/202417/05/20240
7626/10/202321/11/20230EA17/05/202424/05/20240
7726/10/202321/11/20230EA24/05/202431/05/20240
7826/10/202321/11/20230EA31/05/20247/06/20240
tarunr9
Cell Formulas
RangeFormula
AE44:AE78AE44=SUMIFS(Z$43:Z$78,X$43:X$78,"<="&AD44,Y$43:Y$78,">="&AC44,AA$43:AA$78,"LM")
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
527
Asbestos_Jen
Asbestos_Jen
Seafox
  • Question Question
Glide Path Calculation
Replies
3
Views
191
RobP
R
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
167
SFCChase
SFCChase
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
275
Raj1979
R
A
  • Locked
  • Question Question
Multiply an array based on date value entered.
Replies
1
Views
56
Fluff
Fluff

Forum statistics

Threads
1,224,734
Messages
6,180,631
Members
452,991
Latest member
JM_000888

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