SUMIFS with EOM Date

ChetanPuri

Board Regular
Joined
Sep 5, 2018
Messages
74
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
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))
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
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

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
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))
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

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

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