excluding Hidden Rows from my =SUMIF formula PLEASE HELP!

**LeatherChair** · 10-15-2012, 04:11 PM

Hello all,

I have been trying to figure this out for the last couple of (work) days and really need help! I am trying create a summary page for inventory where I can exclude shipped items (Hidden cells; I hid the cells manually). I have been searching online for formulas and found/edited the following:

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($F$1:$F$1841,1,1),ROW($F$1:$F$1841)-ROW(INDEX($F$1:$F$1841,1,1)),0))=1),--($H$1:$H$1841=B1847),$F$1:$F$1841)

However, I then realized that this formula only excludes hidden rows that were AUTO hidden from a data filter. PLEASE HELP ME! Is there a formula that omits the rows I hid myself?!!

((( My original sumif formula was =SUMIF($H$1:$H$1841,B1847,$F$1:$F$1841) )))

**djapigo** · 10-15-2012, 06:27 PM

Hi LeatherChair,

Have you tried looking into Pivot Tables? Or even changing you data into a Table with a Total Row?

Let me know if you need explanation.

- Dennis

**Haseeb Avarakkan** · 10-15-2012, 09:04 PM

Use 103 instead of 3 in SUBTOTAL,

SUBTOTAL(103

**LeatherChair** · 10-16-2012, 09:04 AM

Originally Posted by Haseeb A

Use 103 instead of 3 in SUBTOTAL,

SUBTOTAL(103

Thank you! It works!! Can you PLEASE explain to me what adding the 10(3) changed?! Why does it work?

**Cutter** · 10-16-2012, 10:23 AM

The 103 code is used when you want to exclude hidden values for a COUNTA().

Here's a link that explains the various codes: http://www.excelfunctions.net/Excel-...-Function.html