SUMIF Visible Range Only

[hellfire45]

Hi Guys,

I realize this has been asked online before but I just can't seem to replicate it for my case. So I will explain my issue. I have a table and I need it to calculate a sumif which updates when I filter a table so that it only counts the visible range on which I have filtered.

So if I were to SUMIF the entire table unfiltered it would look like =sumif($U$16:$U$102,$Y2,$R$16:$R$102)

So I found this online and tried to replicate it using the below formula but it doesn't work:
=SUMPRODUCT(($U$16:$U$102=$Y2)+0,SUBTOTAL(109,OFFSET($R$16:$R$102,ROW($R$16:$R$102)-MIN(ROW($R$16:$R$102)),0,1,1)))

Can anybody offer a solution based on the SUMIF i entered above? And I apologize for the redundancy against the existing literature. Thank you!

 

[Fluff]

How about

=SUMPRODUCT(SUBTOTAL(109,OFFSET($R$16,ROW($R$16:$R$102)-ROW($R$16),0)),--($U$16:$U$102=$Y2))

 

[hellfire45]

How about

=SUMPRODUCT(SUBTOTAL(109,OFFSET($R$16,ROW($R$16:$R$102)-ROW($R$16),0)),--($U$16:$U$102=$Y2))

Perfect Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[bigu]

sorry for the stupid question in this old thread, but I think the stupid question is that question that is not asked ... so ... what is the difference between

=SUMPRODUCT(SUBTOTAL(109,OFFSET($R$16,ROW($R$16:$R$102)-ROW($R$16),0)),--($U$16:$U$102=$Y2))

and

=SUMPRODUCT(($U$16:$U$102=$Y2)+0,SUBTOTAL(109,OFFSET($R$16:$R$102,ROW($R$16:$R$102)-MIN(ROW($R$16:$R$102)),0,1,1)))

 

[Fluff]

Not much really

 

[NileshAPatel]

what's if the range is variable, instead of $R$16:$R$102 on next day range get changed to $R$16:$R$200