SUMIFS help

[Full Spool]

I am trying to count information from one sheet to another and display information in the rows I have labels for but keep getting values of zero.
I have in column H-S the month and year on sheet one, I am trying to take the values of column J in the Glasgow_Reading_Export tab and display
them in those fields for column H-S on sheet one. However I am having issues with my formula I can't seem to resolve. Can anyone offer any guidance?

TIA.

Latest version of office 365

test book.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Meter Number	Account Number	Name	Address	City	State	Zip	Month	Year	Monthly Usage	Yearly Usage	Month YR
2	36424-1	0001-00010-007	Test	313 S Green St	Glasgow	KY	42141	3	2021	2200	90400	32021
3	36424-1	0001-00010-007	Test	313 S Green St	Glasgow	KY	42141	4	2021	8300	90400	42021
4	36424-1	0001-00010-007	Test	313 S Green St	Glasgow	KY	42141	5	2021	6300	90400	52021
5	36424-1	0001-00010-007	Test	313 S Green St	Glasgow	KY	42141	6	2021	5300	90400	62021
6	36424-1	0001-00010-007	Test	313 S Green St	Glasgow	KY	42141	7	2021	10400	90400	72021
7	36424-1	0001-00010-007	Test	313 S Green St	Glasgow	KY	42141	8	2021	9700	90400	82021
8	36424-1	0001-00010-007	Test	313 S Green St	Glasgow	KY	42141	9	2021	6200	90400	92021
9	36424-1	0001-00010-007	Test	313 S Green St	Glasgow	KY	42141	10	2021	13200	90400	102021
10	36424-1	0001-00010-007	Test	313 S Green St	Glasgow	KY	42141	11	2021	7800	90400	112021
11	36424-1	0001-00010-007	Test	313 S Green St	Glasgow	KY	42141	12	2021	8200	90400	122021
12	36424-1	0001-00010-007	Test	313 S Green St	Glasgow	KY	42141	1	2022	6600	90400	12022
13	36424-1	0001-00010-007	Test	313 S Green St	Glasgow	KY	42141	2	2022	6200	90400	22022
Glasgow_ReadingExport20220330

Cell Formulas
Range	Formula
L2:L13	L2	=CONCAT(H2,I2)*1

test book.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2	Meter Number	Account Number	Name	Address	City	State	Zip	12022	22022	32022	42021	52021	62021	72021	82021	92021	102021	112021	122021
3	36424-1	0001-00010-007	Test	313 S Green St	Glasgow	KY	42141	#VALUE!
Sheet1

Cell Formulas
Range	Formula
H3	H3	=SUMIFS('https://glasgowwater-my.sharepoint.com/personal/jrichardson_glasgowh2o_com/Documents/Desktop/[Billing Report for Water Model 2022.xlsx]Glasgow_ReadingExport20220330'!$L:$L,'https://glasgowwater-my.sharepoint.com/personal/jrichardson_glasgowh2o_com/Documents/Desktop/[Billing Report for Water Model 2022.xlsx]Glasgow_ReadingExport20220330'!$B:$B,'https://glasgowwater-my.sharepoint.com/personal/jrichardson_glasgowh2o_com/Documents/Desktop/[Billing Report for Water Model 2022.xlsx]Sheet1'!B3,'https://glasgowwater-my.sharepoint.com/personal/jrichardson_glasgowh2o_com/Documents/Desktop/[Billing Report for Water Model 2022.xlsx]Glasgow_ReadingExport20220330'!$J:$J,'https://glasgowwater-my.sharepoint.com/personal/jrichardson_glasgowh2o_com/Documents/Desktop/[Billing Report for Water Model 2022.xlsx]Sheet1'!H$2)

 

[KRice]

Try this, but adjust the sheet name to your file. My summary sheet is Sheet5, yours is Sheet1. Are both of these worksheets in the same workbook? You shouldn't need to put the full path in the formula.

MrExcel_20220419.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2	Meter Number	Account Number	Name	Address	City	State	Zip	12022	22022	32022	42021	52021	62021	72021	82021	92021	102021	112021	122021
3	36424-1	0001-00010-007	Test	313 S Green St	Glasgow	KY	42141	6600	6200	0	8300	6300	5300	10400	9700	6200	13200	7800	8200
Sheet5

Cell Formulas
Range	Formula
H3:S3	H3	=SUMIFS( Glasgow_ReadingExport20220330!$J:$J, Glasgow_ReadingExport20220330!$L:$L, Sheet5!$H$2:$S$2, Glasgow_ReadingExport20220330!$B:$B, Sheet5!$B3 )
Dynamic array formulas.

 

[Full Spool]

Try this, but adjust the sheet name to your file. My summary sheet is Sheet5, yours is Sheet1. Are both of these worksheets in the same workbook? You shouldn't need to put the full path in the formula.

MrExcel_20220419.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2	Meter Number	Account Number	Name	Address	City	State	Zip	12022	22022	32022	42021	52021	62021	72021	82021	92021	102021	112021	122021
3	36424-1	0001-00010-007	Test	313 S Green St	Glasgow	KY	42141	6600	6200	0	8300	6300	5300	10400	9700	6200	13200	7800	8200
Sheet5

Cell Formulas
Range	Formula
H3:S3	H3	=SUMIFS( Glasgow_ReadingExport20220330!$J:$J, Glasgow_ReadingExport20220330!$L:$L, Sheet5!$H$2:$S$2, Glasgow_ReadingExport20220330!$B:$B, Sheet5!$B3 )
Dynamic array formulas.

That appears to work except for MonthYR 32021 the value should be 2200 but it's displaying "0" . Yes, the information is in the same workbook.

 

[Fluff]

for MonthYR 32021 the value should be 2200 but it's displaying "0"

That's because in your Glasgow_ReadingExport20220330 sheet you have 32021, but in Sheet1 J2 you have 32022

 

[Full Spool]

That's because in your Glasgow_ReadingExport20220330 sheet you have 32021, but in Sheet1 J2 you have 32022

I'm an idiot, thanks for pointing out the typo!

Appreciate all the help!

 

[Fluff]

My pleasure.

 

[Alex Blakenburg]

@Full Spool - since it was a data problem this is not relevant to fixing the problem but from a troubleshooting perpective you would make it much easier for yourself if you.
1) Work with the other workbook open so that you don't see the full path and only the workbook name and sheet name.
2) Assuming your formula is on Sheet 1, it doesn't need the full path name or even the sheet name

This means you only need to trouble shoot using something that looks like this:

=SUMIFS(
    '[Billing Report for Water Model 2022.xlsx]Glasgow_ReadingExport20220330'!$J:$J,
     '[Billing Report for Water Model 2022.xlsx]Glasgow_ReadingExport20220330'!$B:$B,$B3,
     '[Billing Report for Water Model 2022.xlsx]Glasgow_ReadingExport20220330'!$L:$L,H$2
    )