Sum horizontal and vertical based on criteria

[cwallace70]

I am trying to get the sum of a product in column A:A with the amounts in columns AD:JN.

I tried this sumif formula
=SUM(IF('Apr 8-CM GL v ACS Recon Detail'!A:A=$A13,$AD:$JN))
and it returns zero.

I tried sumproduct
=SUMPRODUCT('Apr 8-CM GL v ACS Recon Detail'!AD:JN,'Apr 8-CM GL v ACS Recon Detail'!A:A=Sumary!A:A)
and it a returned a #N/A

I hope someone can help me with these formulas.

On sheet 1 a product A in col A:A is equal to a product on sheet 2 col A:A, then sum AD:JN

Thank you

 

[Oscar Cronquist]

I tried this sumif formula
=SUM(IF('Apr 8-CM GL v ACS Recon Detail'!A:A=$A13,$AD:$JN))

To make that work you need to enter that as an array formula.

1. Type the formula in a cell
2. Press and hold CTRL and SHIFT
3. Press Enter

Your formula has now a beginning and ending curly bracket. {=formula}
Don't eneter these characters yourself.

https://exceljet.net/glossary/array-formula

	A​	B​	C​	D​	E​	F​	G​	H​
1​		A	1​	5​			A	16​
2​		B	2​	6​
3​		A	3​	7​
4​		D	4​	8​

Sheet: Sheet83

Array formula in cell H1:
=SUM(IF(B1:B4=G1,C1:D4,0))