How to use Sumproduct to ignore hidden rows

[Sheevamy]

Hello there,

Need help on sumproduct formula that only count on visible cells & excludes any hidden rows.

I tried using this formula to the sample provided below:-
=SUMPRODUCT(--(YEAR(C3:C7)=2006)

However, it returns with overall 2006 dates, which means it's also counting those hidden cells. When I select the Type of Site as Sharing, I get 4 instead of 2. Any help on this please.

4
Site No Type Quotations date
Site 1 Sharing 11-Jan-06
Site 2 Non Sharing 05-Jan-06
Site 3 Sharing 07-Jan-06
Site 4 Sharing 20-Dec-05
Site 5 Non Sharing 03-Jan-06

 

[Brian from Maui]

Are the rows hidden because of using auto filter?

O use another condition in the Sumproduct formula.

 

[Sheevamy]

Yes, the rows are hidden due to autofilter.

 

[Brian from Maui]

See if this thread helps,

http://www.mrexcel.com/board2/viewtopic.php?t=193298

Or as I said earlier, add another condition to the Sumproduct formula. What was the criteria to filter?

 

[Sheevamy]

Hi Brian,

Thanks for helping. Based from your link I tried the following formula & the value returns as 0.

=SUMPRODUCT(SUBTOTAL(2,OFFSET(AN8:AN7000,ROW(AN8:AN7000)-MIN(ROW(AN8:AN7000)),,1)),--(AN8:AN7000=2006))

My original spreadsheet has various columns ranging from 1996 - 2006 & extending to few thousand rows.

Scenario of my spreadsheet:
1) 2005 sites list - most of the activities were completed in 2005, but there are a few activities that were carried forward to this year, and completed in month of Jan/Feb 2006. Thus, it’s updated with current date & year completion.

2) 2006 sites list - also, a few of the activities were already started & completed in 2005.

My criteria is to select for the year of 2006 sites list & count the activities that was completed in 2006 only. However, the earlier sumproduct formula was capturing overall 2006 completion, including from the filtered 2005 sites list, and where else the above is giving me 0 value.

Any modification to the above formula or new conditions?

 

[Aladin Akyurek]

See if this

=SUMPRODUCT(SUBTOTAL(2,OFFSET(AN8:AN7000,ROW(AN8:AN7000)-ROW(AN8:AN7000),,1)),--(YEAR(AN8:AN7000)=2006)))

yields the desired count.

 

[Sheevamy]

Have tried that & the result is still 0.

The closest formula is:-
1)=SUMPRODUCT(--(YEAR(AN8:AN7000)=2006)) or
2)=COUNT(IF(AN$8:AN$7000<>"",IF(YEAR(AN$8:AN$7000)=2006,1)))

but both does not exclude/ignore to hidden rows.

 

[Aladin Akyurek]

Have tried that & the result is still 0.

The closest formula is:-
1)=SUMPRODUCT(--(YEAR(AN8:AN7000)=2006)) or
2)=COUNT(IF(AN$8:AN$7000<>"",IF(YEAR(AN$8:AN$7000)=2006,1)))

but both does not exclude/ignore to hidden rows.

What happens if you try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(AN8:AN7000,ROW(AN8:AN7000)-ROW(ANS8),,1)),--(YEAR(AN8:AN7000)=2006))

 

[Sheevamy]

I get the error message...#NAME?

 

[Aladin Akyurek]

I get the error message...#NAME?

=SUMPRODUCT(SUBTOTAL(3,OFFSET(AN8:AN7000,ROW(AN8:AN7000)-ROW(AN8),,1)),--(YEAR(AN8:AN7000)=2006))