Need to subtotal a sumif formula

[Clooney]

=SUMIF(A28:A5076,A2,H28:H5076)+SUMIF(A28:A5076,A2,I28:I5076)

If I filter this data I want this formula to reflect only the filtered data and not the entire column.

 

[Andrew Poulsom]

You can use SUBTOTAL with SUMPRODUCT. here's a tutorial:

Excel Function Friday: Subtotal and Sumproduct with Filter | Contextures Blog

 

[Clooney]

I must be an idiot because I couldnt figure that out.

 

[general_payne]

Hi,

Sumproduct and Subtotal can get incredibly confusing. Try this:

=(SUMPRODUCT(SUBTOTAL(3,OFFSET(H$28,ROW($H$28:$H$5076)-ROW($H$28),,1)),--($A$28:$A$5076=A2),$H$28:$H$5076))+(SUMPRODUCT(SUBTOTAL(3,OFFSET(I$28,ROW($I$28:$I$5076)-ROW($I$28),,1)),--($A$28:$A$5076=A2),$I$28:$I$5076))

Thanks

 

[Clooney]

Hi,

Sumproduct and Subtotal can get incredibly confusing. Try this:

=(SUMPRODUCT(SUBTOTAL(3,OFFSET(H$28,ROW($H$28:$H$5076)-ROW($H$28),,1)),--($A$28:$A$5076=A2),$H$28:$H$5076))+(SUMPRODUCT(SUBTOTAL(3,OFFSET(I$28,ROW($I$28:$I$5076)-ROW($I$28),,1)),--($A$28:$A$5076=A2),$I$28:$I$5076))

Thanks

Sweet that did the trick. Thank you very much!!