SUMIFS - FAILURE TO RECOGNISE DOUBLE DIGIT NUMERICAL CRITERIA

[Gerry Hunt]

Can somebody help please?
I am using SUMIFS to collect financial information related to my Home Accounts into a 'Summary of Accounts' worksheet from 6 different Excel worksheets ['Account Bank Statements'].
Each worksheet is examined by SUMIFS to extract the 'Value' related to 64 different Categories of Expenditure (Account Codes) and the related 'Month Number' [i.e. 1-12 (Jan to Dec)]. The result is a month by month presentation of income and expenditure related to all 64 Account Codes.
The formula train given below works perfectly for Months 1 - 9 [i.e. Jan to Sep] but refuses to work for months 10-12 (Oct - Dec) - i.e a "double digit" rather than 'single digit' month number - in this case it merely returns zeroes. The formula train below is for "October" i.e Month "10" [second given criteria] and is applied in the 'Summary of Accounts' Sheet to each Category of Income/Expenditure [Account Code]. In this October example it points Account Code "$C9" in the Summary sheet. I do hope this is sufficiently clear information to permit some kind person to propose a solution to this problem - Thank you in anticipation.

=SUMIFS('JOINT CURRENT'!$D$6:$D$2000,'JOINT CURRENT'!$H$6:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,'JOINT CURRENT'!$K$6:$K$2000,10)
+SUMIFS(CASH!$D$6:$D$2000,CASH!$H$6:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,CASH!$K$6:$K$2000,10)
+SUMIFS('NATWEST CC'!$H$7:$H$2000,'NATWEST CC'!$J$7:$J$2000,'SUMMARY OF ACCOUNTS'!$C9,'NATWEST CC'!$L$7:$L$2000,10)
+SUMIFS('M&S CC'!$D$7:$D$2000,'M&S CC'!$H$7:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,'M&S CC'!$K$7:$K$2000,10)
+SUMIFS('HALIFAX CC'!$D$6:$D$2000,'HALIFAX CC'!$H$6:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,'HALIFAX CC'!$K$6:$K$2000,10)
+SUMIFS('WISE DC'!$H$7:$H$2000,'WISE DC'!$J$7:$J$2000,'SUMMARY OF ACCOUNTS'!$C9,'WISE DC'!$M$7:$M$2000,10)

 

[SanjayGMusafir]

=SUMIFS('JOINT CURRENT'!$D$6:$D$2000,'JOINT CURRENT'!$H$6:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,'JOINT CURRENT'!$K$6:$K$2000,10)
+SUMIFS(CASH!$D$6:$D$2000,CASH!$H$6:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,CASH!$K$6:$K$2000,10)
+SUMIFS('NATWEST CC'!$H$7:$H$2000,'NATWEST CC'!$J$7:$J$2000,'SUMMARY OF ACCOUNTS'!$C9,'NATWEST CC'!$L$7:$L$2000,10)
+SUMIFS('M&S CC'!$D$7:$D$2000,'M&S CC'!$H$7:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,'M&S CC'!$K$7:$K$2000,10)
+SUMIFS('HALIFAX CC'!$D$6:$D$2000,'HALIFAX CC'!$H$6:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,'HALIFAX CC'!$K$6:$K$2000,10)
+SUMIFS('WISE DC'!$H$7:$H$2000,'WISE DC'!$J$7:$J$2000,'SUMMARY OF ACCOUNTS'!$C9,'WISE DC'!$M$7:$M$2000,10)

There can be a better workaround to your problem. Just want to have a look at how you are handling (inputting) dates in your various sheets.

Share a screenshot of the same to understand and help you better

 

[Gerry Hunt]

Thanks for your response. As you will appreciate, I don't wish to show the full detail of my personal financial accounting on-line but I hope the following extract from my Current Account worksheet will prove useful. The month number is derived from the item entry date as follows: "=IF(A7="","",MONTH(A7))". I looked at XL2BB - this cannot be added without exiting Protected Mode. A Microsoft warning indicated that it did not trust the source code. Are you able to help further with this?
Best regards.

Date	Type	Description	Value	Balance	Account Code	Notes	Tags	Month
01/01/2022​		OPENING BALANCE	4,967.36​	4,967.36​	1.1
04/01/2022​	DPC	P SM MAYNARD , G C HUNT , VIA MOBILE - PYMT , FP 03/01/22 10 , 17093232160609000N	-72.59​	4,894.77	4.4	Lunch at Five Arows		1
04/01/2022​	DPC	P SM MAYNARD , G C HUNT , VIA MOBILE - PYMT , FP 03/01/22 10 , 17093232160609000N	-26.59​	4,868.18	7.1	Waddesdon Gift Shop Port and Wine		1
04/01/2022​	DPC	P SM MAYNARD , G C HUNT , VIA MOBILE - PYMT , FP 03/01/22 10 , 17093232160609000N	-15.79​	4,852.39	11.3	Waddesdon Gift Shop Cards		1
04/01/2022​	POS	3410 31DEC21 C , BOOTS,BERKHAMSTED , BERKHAMSTED GB	-6.49​	4,845.90	10.1	Mouth Ulcer Lotion		1
04/01/2022​	POS	3410 31DEC21 C , WAITROSE 223 , BERKHAMSTED GB	-4.50​	4,841.40	7.1	Groceries		1
04/01/2022​	POS	3410 03JAN22 C , TESCO STORE 3309 , TRING GB	-27.04​	4,814.36	7.1	Groceries		1
04/01/2022​	D/D	E.ON NEXT	-226.00​	4,588.36	14.2	Energy		1
04/01/2022​	D/D	DORS&SOM AA	-4.34​	4,584.02	12.1	Charity		1
04/01/2022​	D/D	SMILE TRAIN	-12.50​	4,571.52	12.1	Charity		1

 

[SanjayGMusafir]

in SUMIFS rather than putting a number for month make it more dynamic.
Underneath is a small extract from my file where I keep everything dynamic. And shall explain below how to do that with your formula.

All Records.xlsb
	Y
61	Date
62	Oct-2022
63	Sep-2022
64	Aug-2022
65	Jul-2022
66	Jun-2022
67	May-2022
Bank

Cell Formulas
Range	Formula
Y62	Y62	=EOMONTH(TODAY(),-1)+1
Y63	Y63	=EOMONTH(TODAY(),-2)+1
Y64	Y64	=EOMONTH(TODAY(),-3)+1
Y65	Y65	=EOMONTH(TODAY(),-4)+1
Y66	Y66	=EOMONTH(TODAY(),-5)+1
Y67	Y67	=EOMONTH(TODAY(),-6)+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Y62:Y80,Y84:Y102	Dates Occurring	this month	text	NO

So with your formula -
=SUMIFS('JOINT CURRENT'!$D$6:$D$2000,'JOINT CURRENT'!$H$6:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,'JOINT CURRENT'!$K$6:$K$2000,10)
+SUMIFS(CASH!$D$6:$D$2000,CASH!$H$6:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,CASH!$K$6:$K$2000,10)
+SUMIFS('NATWEST CC'!$H$7:$H$2000,'NATWEST CC'!$J$7:$J$2000,'SUMMARY OF ACCOUNTS'!$C9,'NATWEST CC'!$L$7:$L$2000,10)
+SUMIFS('M&S CC'!$D$7:$D$2000,'M&S CC'!$H$7:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,'M&S CC'!$K$7:$K$2000,10)
+SUMIFS('HALIFAX CC'!$D$6:$D$2000,'HALIFAX CC'!$H$6:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,'HALIFAX CC'!$K$6:$K$2000,10)
+SUMIFS('WISE DC'!$H$7:$H$2000,'WISE DC'!$J$7:$J$2000,'SUMMARY OF ACCOUNTS'!$C9,'WISE DC'!$M$7:$M$2000,10)

If we can put dynamic dates in Column Say E9 and below - Just like in Y62 and below in my XL2BB

then your formula can become -

=SUMIFS('JOINT CURRENT'!$D$6:$D$2000,'JOINT CURRENT'!$H$6:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,'JOINT CURRENT'!$K$6:$K$2000,">="&E9,JOINT CURRENT'!$K$6:$K$2000,"<="&EOMONTH(E9,0))
+SUMIFS(CASH!$D$6:$D$2000,CASH!$H$6:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,CASH!$K$6:$K$2000,">="&E9,CASH!$K$6:$K$2000,">="&EOMONTH(E9,0))
+SUMIFS('NATWEST CC'!$H$7:$H$2000,'NATWEST CC'!$J$7:$J$2000,'SUMMARY OF ACCOUNTS'!$C9,'NATWEST CC'!$L$7:$L$2000,">="&E9,'NATWEST CC'!$L$7:$L$2000,">="&EOMONTH(E9,0))
+SUMIFS('M&S CC'!$D$7:$D$2000,'M&S CC'!$H$7:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,'M&S CC'!$K$7:$K$2000,">="&E9,'M&S CC'!$K$7:$K$2000,">="&EOMONTH(E9,0))
+SUMIFS('HALIFAX CC'!$D$6:$D$2000,'HALIFAX CC'!$H$6:$H$2000,'SUMMARY OF ACCOUNTS'!$C9,'HALIFAX CC'!$K$6:$K$2000,">="&E9,'HALIFAX CC'!$K$6:$K$2000,">="&EOMONTH(E9,0))
+SUMIFS('WISE DC'!$H$7:$H$2000,'WISE DC'!$J$7:$J$2000,'SUMMARY OF ACCOUNTS'!$C9,'WISE DC'!$M$7:$M$2000,">="&E9,'WISE DC'!$M$7:$M$2000,">="&EOMONTH(E9,0))

 

[Gerry Hunt]

Dear Sanjay Gulati Musafir - Very many thanks for your input to my problem and your proposed solution. Having looked at my business further, I have decided to move away from extensive use of SUMIFS and to move to using Pivot Tables. Accordingly, I am closing down this problem. All the very best and thank you again. Gerry Hunt.