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


[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td]A[/td][td]

1​

[/td][td]

5​

[/td][td][/td][td][/td][td]A[/td][td]

16​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td][/td][td]B[/td][td]

2​

[/td][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td]A[/td][td]

3​

[/td][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td]D[/td][td]

4​

[/td][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet83[/td][/tr][/table]

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