SUMIF Visible Range Only

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
447
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!
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=SUMPRODUCT(SUBTOTAL(109,OFFSET($R$16,ROW($R$16:$R$102)-ROW($R$16),0)),--($U$16:$U$102=$Y2))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,988
Messages
5,575,390
Members
412,659
Latest member
oliverreyes
Top