SUMIF HLOOKUP

[biglb79]

does anyone know the formula I would use to sum anything coded to this specific GL number? The GL numbers are in row 4, A4:X4 to be specific

The data I would be pulling from is on the invoice detail tab with the account numbers in column C and amount in column D right now the data only goes 100 rows but that could increase moving forward

Thanks in advance!

 

[nuckfuts]

Sheet1: contains GL numbers (unique) in row 4
Sheet2: contains GL numbers and amounts in columns C and D, respectively

In cell A3 type: =SUMPRODUCT(--(Sheet2!$C$C=Sheet1!A4),Sheet2!$D:$D))
Then drag that formula to the right and the total amount for that GL number will populate above it. Will update as data is added to columns C & D.

Hope this helps!

 

[biglb79]

Sheet1: contains GL numbers (unique) in row 4
Sheet2: contains GL numbers and amounts in columns C and D, respectively

In cell A3 type: =SUMPRODUCT(--(Sheet2!$C$C=Sheet1!A4),Sheet2!$D:$D))
Then drag that formula to the right and the total amount for that GL number will populate above it. Will update as data is added to columns C & D.

Hope this helps!

that was correct and I even changed the sheet names to sheet1 and sheet2, but I'm receiving this error message

 

[Fluff]

The formula is missing a colon

=SUMPRODUCT(--(Sheet2!$C:$C=Sheet1!A4),Sheet2!$D:$D)

But you should not use entire column references in array formulae.

 

[nuckfuts]

that was correct and I even changed the sheet names to sheet1 and sheet2, but I'm receiving this error message

View attachment 25507

Whoops - typed too fast and forgot a parenthesis and colon (as mentioned above)

=SUMPRODUCT(--(Sheet2!$C:$C=Sheet1!A4),(Sheet2!$D:$D))

That should do it

 

[nuckfuts]

But you should not use entire column references in array formulae.

Curious why not? I use this formula all the time and haven't had any issues yet but open to suggestions if there's a better way.

 

[Fluff]

The formula you have suggested is processing almost 2.1 million cells, which can seriously slow down a file, especially if you have a lot of cells using that type of formula.
It would be far better to limit the ranges like

=SUMPRODUCT(--(Sheet2!$C2:$C1000=A4),Sheet2!$D2:$D1000)

Its also best not to refer to the sheetname of the sheet that contains the formula, as that can occasionally cause problems.

 

[biglb79]

The formula you have suggested is processing almost 2.1 million cells, which can seriously slow down a file, especially if you have a lot of cells using that type of formula.
It would be far better to limit the ranges like

=SUMPRODUCT(--(Sheet2!$C2:$C1000=A4),Sheet2!$D2:$D1000)

Its also best not to refer to the sheetname of the sheet that contains the formula, as that can occasionally cause problems.

thanks so much! sorry I had to leave work yesterday before updating this

 

[nuckfuts]

The formula you have suggested is processing almost 2.1 million cells, which can seriously slow down a file, especially if you have a lot of cells using that type of formula.
It would be far better to limit the ranges like

=SUMPRODUCT(--(Sheet2!$C2:$C1000=A4),Sheet2!$D2:$D1000)

Its also best not to refer to the sheetname of the sheet that contains the formula, as that can occasionally cause problems.

Ah agreed, just didn't want there to be confusion as to what "A4" I was referencing.