Why is my sumproduct with indirect forumla only returning the first result? Google Sheets

[korfballchick]

Hi,
I have multiple tabs which contain stats from different sports matches.
I want to create a summary sheet which sums data from the various columns based on the name of the athlete.

I have used this formula:
=ArrayFormula(SUMPRODUCT(SUMIF(INDIRECT("'"&WalesMatches&"'!A7:A25"),A14,INDIRECT("'"&WalesMatches&"'!L7:L25"))))

In which "WalesMatches" is a named range for the separate tabs.
A7:A25 is where the name of the athlete is entered on the various tabs
L7:L25 is the total of shots scored on the various tabs

Can anyone explain why this isn't returning the sum of all tab? I'm only getting the first tab's total (i.e. if I change the tabs contained in the named range, I still only get the total from the first tab to pull through, not the sum of all tabs).

 

[CA_Punit]

How is the formula even giving the sum of "First Tab"

Can you help me by posting your data using xl2bb. I have doubt because Sumif's range argument doesn't accept formula range!!!

 

[CA_Punit]

Also Indirect function doesn't accept range

 

[CA_Punit]

I figured it out

So Sumif range argument doesnt accept range generated by formula other than Indirect. However Indirect function doesn't accept named ranges accept only one criteria.
So you have to break down the Indirect part of it

Balance Quantity.xlsx
	J	K	L	M	N	O	P
3		Named Ranges	VALUES IN SHEETS
4	Sheet	1	1	2	3	4
5	Sheet	2	5	6	7	8
6	Sheet	3	0
7
8
9
10	Criteria
11	2	26
12
Sheet5

Cell Formulas
Range	Formula
L4:O5	L4	=TRANSPOSE(INDIRECT("'"&K4&"'!A1:A4"))
L6	L6	=IFERROR(TRANSPOSE(INDIRECT("'"&K6&"'!A1:A4")),0)
K11	K11	=SUM(IF(K4:K6=J11,$L$4:$O$6))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Named	=Sheet5!$K$4:$K$5	L4, K11

 

[Fluff]

Is this an Excel or Google Sheets question?

 

[CA_Punit]

Is the working different???

Does Google Sheet cover Multiple Range in Indirect???

 

[Fluff]

If this is for Excel, just use
=SUMPRODUCT(SUMIF(INDIRECT("'"&WalesMatches&"'!A7:A25"),A14,INDIRECT("'"&WalesMatches&"'!L7:L25")))

 

[CA_Punit]

So @Fluff please help me figure out as how is this working

also i tried to solve it using a tubular format

Balance Quantity.xlsx
	A	B	C	D	E	F
1			Players Name
2			Punit	Ayush	Rahul
3	Sheet Name----->	Match1	1	3	2
4		Match2	11	10	12
5		Match3
6		Match4
7		Total	12	13	14
8
9
10			Punit	12
11
12
Sheet5

Cell Formulas
Range	Formula
C3:E6	C3	=IFERROR(VLOOKUP(C$2,INDIRECT($B3&"!A2:B4"),2,0),"")
C7:E7	C7	=SUM(C3:C6)
D10	D10	=INDEX($C$7:$E$7,MATCH(C10,$C$2:$E$2,0))

Named Ranges
Name	Refers To	Cells
Wales_M	=Sheet5!$B$3:$B$6	C3:E3

Balance Quantity.xlsx
	A	B
1	Name	Scores
2	Punit	1
3	Rahul	2
4	Ayush	3
Match1

Balance Quantity.xlsx
	A	B
1	Name	Scores
2	Ayush	10
3	Punit	11
4	Rahul	12
5
Match2

 

[korfballchick]

It's in googlesheets- does that make a difference?

 

[Fluff]

Yes it does, whilst there are a lot of similarities, they are not the same.
In future you should specify that it's for Google Sheets & it needs to be posted in the "General Discussion & Other Applications" section.