COUNTIFS for visible cells only

**bibu** · 04-11-2014, 01:56 AM

Hi, I am looking for a COUNTIFS that is only looking only at the visible cells. I have found a few threads on this subject but none for this specific problem. So if an UDF for this won't be very complicated, please help.
I know there is a SUMPRODUCT / SUBTOTAL excel solution for COUNTIFSing visible cells only but it doesn't make any sense to me..
I found this to be working but I don't know how modify it to take multiple ranges and criteria like CONUTIFS does

Please Login or Register  to view this content.

**adyteo** · 04-11-2014, 02:53 AM

You need a helper column (let's say M) where you type: =SUBTOTAL(103,RefCell) where RefCell is where you filter by.

then, for COUNTIF do this: =COUNTIF(M:M,1)

this will count for you only the visible rows.

**bibu** · 04-11-2014, 03:04 AM

Thanks for the idea I will look into it
Will this work for COUNTIFS also?

**adyteo** · 04-11-2014, 03:27 AM

yes, it is also applicable for COUNTIFS

**bibu** · 04-12-2014, 12:03 PM

I have found this piece of code, is there any way to make it work for COUNTIFS?

Please Login or Register  to view this content.

**bibu** · 04-13-2014, 09:10 AM

Adyteo, if only I had listened to you in the first place... but you lost me on the "RefCell part and I didn't want to bother you with questions.
So this solved by adding a helper column with a SUBTOTAL(103,A1) for example ,that will determine if the cell you are interested in is hidden or not.
As for the modified UDF part, it's very complicated and not worth the trouble.

**oldhack** · 04-14-2014, 09:53 PM

Thanks bibu. That code was very helpful.

COUNTIFS for visible cells only

LinkBack

Thread Tools

Rate This Thread

Display

COUNTIFS for visible cells only

Re: COUNTIFS for visible cells only

Re: COUNTIFS for visible cells only

Re: COUNTIFS for visible cells only

Re: COUNTIFS for visible cells only

Re: COUNTIFS for visible cells only

Re: COUNTIFS for visible cells only

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] COUNTIFS on visible cells only

[SOLVED] use countifs ( using condition) only on visible rows

Code for Countifs on visible cells only

Excel 2007 : COUNTIF and COUNTIFS on Visible Cells Only

COUNTIFS on Visible Cells only

Bookmarks

Bookmarks

Posting Permissions