Conditional CountIf (need to use Autofilters)

**FAHall** · 03-14-2011, 03:34 PM

I want to utilize my COUNTIF function with other conditional criteria through autofilter.

Here is my function:

=COUNT(IF(FREQUENCY(MATCH(C7:C439,C7:C439,0),MATCH(C7:C439,C7:C439,0))>0,1))

This is used to count unique employee #s in column C from row 7-439, and it works for getting me a total regardless of filters, but I need to get filtered totals as well.

I believe there is a way to do this using a SUMPRODUCT function, but I can't seem to figure it out.

Can someone please point me in the right direction for making the above function compatible with filters?

Thanks,
-Alex

**NBVC** · 03-14-2011, 04:02 PM

Try:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(C7:C439,ROW(C7:C4390)-ROW(C7),0,1)),MATCH(C7:C439,C7:C439,0)),ROW(C7:C439)-ROW(C7)+1),1))

confirmed with CTRL+SHIFT+ENTER not just ENTER

**FAHall** · 03-14-2011, 04:21 PM

That gets me #N/A

I should have specified that the values in the column are number and letters (e.g. H57).

I tried converting the SUM to COUNT, but that just got me "0", which I guess is better than #N/A, but still not what I want.

=)

Thanks,
-Alex

**NBVC** · 03-14-2011, 04:24 PM

One of my arrays is a little to big.. fat fingers...

Try:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(C7:C439,ROW(C7:C439)-ROW(C7),0,1)),MATCH(C7:C439,C7:C439,0)),ROW(C7:C439)-ROW(C7)+1),1))

**FAHall** · 03-14-2011, 04:27 PM

Worked like a charm!

Thank you very much for the help. It is much appreciated.

Thanks,
-Alex

Thread: Conditional CountIf (need to use Autofilters)

Thread Tools

Display

Conditional CountIf (need to use Autofilters)

Re: Conditional CountIf (need to use Autofilters)

Re: Conditional CountIf (need to use Autofilters)

Re: Conditional CountIf (need to use Autofilters)

Re: Conditional CountIf (need to use Autofilters)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions