SUMIFS with EOM Date

C

ChetanPuri

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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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 Question
SUMIFS EOM
Replies
2
Views
284
ChetanPuri
C
C
  • Question Question
sumifs WITH eom DOESN'T WORK
Replies
3
Views
240
Peter_SSs
Peter_SSs
S
  • Solved
SumIf problem
Replies
2
Views
366
sharshra
S
J
  • Solved
Using Month query inside sumif formula
Replies
6
Views
736
Jpull
J
S
  • Question Question
Trouble with macro to delete rows
2
Replies
14
Views
242
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

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