SUMIFS with EOM Date

C

ChetanPuri

Board Regular
Joined
Sep 5, 2018
Messages
60
Office Version
  1. 365
Platform
  1. Windows
Good Morning Excel Gurus,
I have question regarding my attached worksheet the First worksheet I named it as Sheet 1 (2) and another tab is called EXCELWORKSHEET. On the First worksheet, In column D I am trying to build a 3 way sumifs, to sum the amounts from source worksheet "EXCELWORKSHEET" , based on Dates COLUMN A 6, Account Description Column D 6 and S/Code E6 onwards. On the first worksheet, I have Account No. starting from A2, Location (S/Code) Column B2 and dates D1 onwards. The issue I get my sumifs formula works fine If I just refer to Dates for EOM, but as soon as I add any other 2 Column A & B, it shows "#Value Error". Any help regarding this is highly appreciated.

Many thanks,
Regards,
Chetan

Book2
ABCDEFGH
1Account No.LocationAccount descriptionJul-2019Aug-2019Sep-2019Oct-2019Nov-2019
220.23.100.620GIR155-SILHealth & Safety#VALUE!
320.23.100.620JOR005-SILHealth & Safety#VALUE!
420.23.100.620KUR024-SILHealth & Safety#VALUE!
520.23.100.620STU009-SILHealth & Safety#VALUE!
620.23.100.620VIC020-SILHealth & Safety#VALUE!
Sheet1 (2)
Cell Formulas
RangeFormula
E1:H1E1=DATE(YEAR(D1),MONTH(D1)+2,0)
D2:D615D2=SUMIFS(ExcelWorksheet!$F:$F,ExcelWorksheet!$B$6:$B$78719,'Sheet1 (2)'!A2:A615,ExcelWorksheet!$A:$A,">="&DATE(YEAR('Sheet1 (2)'!D$1),MONTH('Sheet1 (2)'!D$1),1),ExcelWorksheet!$A:$A,"<="&EOMONTH('Sheet1 (2)'!D$1,0))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R1:AD1,D1:P1Expression=$D$2>#REF!textNO



Book2
ABCDEFG
1Process Date:
2From Period Start:
3
4To Period End:1
5DateAccount No.Cost CodeAccount DescriptionS/CodeDebitCredit
67-Jul-1910.10.100.604604Alarm & SecurityPAL010745.00
711-Jul-1910.10.100.604604Alarm & SecurityPAL010727.04
825-Jul-1910.10.100.604604Alarm & SecurityPAL010160.00
93-Jul-1910.10.100.604604Alarm & SecurityWAG01065.46
1030-Jul-1910.10.100.604604Alarm & SecurityWAG01067.37
117-Aug-1910.10.100.604604Alarm & SecurityWAG010160.00
1223-Sep-1910.10.100.604604Alarm & SecurityKAT01090.00
133-Oct-1910.10.100.604604Alarm & SecurityPAL010200.00
148-Nov-1910.10.100.604604Alarm & SecurityWAG01013.35
1531-Jul-1910.10.100.609609Cleaning & Pest Control72.44
ExcelWorksheet
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
try making criteria1 a single value, not the whole column A:
Excel Formula: 
=SUMIFS(ExcelWorksheet!$F:$F,ExcelWorksheet!$B$6:$B$78719,'Sheet1 (2)'!A2,ExcelWorksheet!$A:$A,">="&DATE(YEAR('Sheet1 (2)'!D$1),MONTH('Sheet1 (2)'!D$1),1),ExcelWorksheet!$A:$A,"<="&EOMONTH('Sheet1 (2)'!D$1,0))
 
Upvote 0
bobsan42 said:
try making criteria1 a single value, not the whole column A:
Excel Formula: 
=SUMIFS(ExcelWorksheet!$F:$F,ExcelWorksheet!$B$6:$B$78719,'Sheet1 (2)'!A2,ExcelWorksheet!$A:$A,">="&DATE(YEAR('Sheet1 (2)'!D$1),MONTH('Sheet1 (2)'!D$1),1),ExcelWorksheet!$A:$A,"<="&EOMONTH('Sheet1 (2)'!D$1,0))
Click to expand...
Sorry still get the same

SIL-Operation Expense Forecast -25 March 2024.xlsx
ABCDE
1Account No.LocationAccount descriptionJul-2019Aug-2019
220.23.100.620GIR155-SILHealth & Safety#VALUE!
Sheet1
Cell Formulas
RangeFormula
E1E1=DATE(YEAR(D1),MONTH(D1)+2,0)
D2D2=SUMIFS(ExcelWorksheet!$F:$F,ExcelWorksheet!$B$6:$B$78719,Sheet1!A2,ExcelWorksheet!$A:$A,">="&DATE(YEAR(Sheet1!D$1),MONTH(Sheet1!D$1),1),ExcelWorksheet!$A:$A,"<="&EOMONTH(Sheet1!D$1,0))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R1:AD1,D1:P1Expression=$D$2>#REF!textNO
 
Upvote 0
ChetanPuri said:
Sorry still get the same

SIL-Operation Expense Forecast -25 March 2024.xlsx
ABCDE
1Account No.LocationAccount descriptionJul-2019Aug-2019
220.23.100.620GIR155-SILHealth & Safety#VALUE!
Sheet1
Cell Formulas
RangeFormula
E1E1=DATE(YEAR(D1),MONTH(D1)+2,0)
D2D2=SUMIFS(ExcelWorksheet!$F:$F,ExcelWorksheet!$B$6:$B$78719,Sheet1!A2,ExcelWorksheet!$A:$A,">="&DATE(YEAR(Sheet1!D$1),MONTH(Sheet1!D$1),1),ExcelWorksheet!$A:$A,"<="&EOMONTH(Sheet1!D$1,0))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R1:AD1,D1:P1Expression=$D$2>#REF!textNO
Click to expand...

or it works if I don't use date formula, refer to Aug-2019 column,
SIL-Operation Expense Forecast -25 March 2024.xlsx
ABCDE
1Account No.LocationAccount descriptionJul-2019Aug-2019
220.23.100.620GIR155-SILHealth & Safety1,470,657.745,522.18
Sheet1
Cell Formulas
RangeFormula
E1E1=DATE(YEAR(D1),MONTH(D1)+2,0)
D2D2=SUMIFS(ExcelWorksheet!$F$6:$F$78719,ExcelWorksheet!$A$6:$A$78719,">="&DATE(YEAR(Sheet1!D$1),MONTH(Sheet1!D$1),1),ExcelWorksheet!$A$6:$A$78719,"<="&EOMONTH(Sheet1!D$1,0))
E2E2=SUMIFS(ExcelWorksheet!$F:$F,ExcelWorksheet!$B:$B,Sheet1!$A:$A,ExcelWorksheet!$E:$E,Sheet1!$B:$B)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R1:AD1,D1:P1Expression=$D$2>#REF!textNO
 
Upvote 0
Well, the problem is also with the sum and criteria ranges - they must all have the same size:
Excel Formula: 
=SUMIFS(ExcelWorksheet!$F:$F,
ExcelWorksheet!$B:$B,'Sheet1 (2)'!$A2,
ExcelWorksheet!$A:$A,">="&DATE(YEAR('Sheet1 (2)'!D$1),MONTH('Sheet1 (2)'!D$1),1),
ExcelWorksheet!$A:$A,"<="&EOMONTH('Sheet1 (2)'!D$1,0))
or
Excel Formula: 
=SUMIFS(ExcelWorksheet!$F$6:$F$78719,
ExcelWorksheet!$B$6:$B$78719,'Sheet1 (2)'!$A2,
ExcelWorksheet!$A$6:$A$78719,">="&DATE(YEAR('Sheet1 (2)'!D$1),MONTH('Sheet1 (2)'!D$1),1),
ExcelWorksheet!$A$6:$A$78719,"<="&EOMONTH('Sheet1 (2)'!D$1,0))
 
Upvote 1
Solution
bobsan42 said:
Well, the problem is also with the sum and criteria ranges - they must all have the same size:
Excel Formula: 
=SUMIFS(ExcelWorksheet!$F:$F,
ExcelWorksheet!$B:$B,'Sheet1 (2)'!$A2,
ExcelWorksheet!$A:$A,">="&DATE(YEAR('Sheet1 (2)'!D$1),MONTH('Sheet1 (2)'!D$1),1),
ExcelWorksheet!$A:$A,"<="&EOMONTH('Sheet1 (2)'!D$1,0))
or
Excel Formula: 
=SUMIFS(ExcelWorksheet!$F$6:$F$78719,
ExcelWorksheet!$B$6:$B$78719,'Sheet1 (2)'!$A2,
ExcelWorksheet!$A$6:$A$78719,">="&DATE(YEAR('Sheet1 (2)'!D$1),MONTH('Sheet1 (2)'!D$1),1),
ExcelWorksheet!$A$6:$A$78719,"<="&EOMONTH('Sheet1 (2)'!D$1,0))
Click to expand...
Thank you so much, the first one worked. Much appreciated
=SUMIFS(ExcelWorksheet!$F:$F,
ExcelWorksheet!$B:$B,'Sheet1 (2)'!$A2,
ExcelWorksheet!$A:$A,">="&DATE(YEAR('Sheet1 (2)'!D$1),MONTH('Sheet1 (2)'!D$1),1),
ExcelWorksheet!$A:$A,"<="&EOMONTH('Sheet1 (2)'!D$1,0))
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
sumifs WITH eom DOESN'T WORK
Replies
3
Views
118
Peter_SSs
Peter_SSs
P
  • Question
Analytics Project - Sumifs
Replies
1
Views
237
bobaol
B
J
  • Question
VBA If Statements between dates
Replies
3
Views
388
SeniorNewbie
SeniorNewbie
M
  • Solved
Formula to 2-way look up a unique identifier and a date, then take the highest number from another column
Replies
3
Views
65
MrsFraser07
M
F
  • Question
VBA code giving error on line .Wrap = wdFindContinue
Replies
2
Views
412
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,215,071
Messages
6,122,964
Members
449,094
Latest member
Anshu121

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