# SUMIF Visible Range Only

#### hellfire45

##### Active Member
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!

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=SUMPRODUCT(SUBTOTAL(109,OFFSET(\$R\$16,ROW(\$R\$16:\$R\$102)-ROW(\$R\$16),0)),--(\$U\$16:\$U\$102=\$Y2))``

#### hellfire45

##### Active Member
Excel Formula:
``=SUMPRODUCT(SUBTOTAL(109,OFFSET(\$R\$16,ROW(\$R\$16:\$R\$102)-ROW(\$R\$16),0)),--(\$U\$16:\$U\$102=\$Y2))``
Perfect Thank you.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
0
Views
93
Replies
0
Views
157
Replies
6
Views
144
Replies
8
Views
163
Replies
5
Views
284