# subtotal countif

#### fletch

##### Board Regular
I've been using the subtotal function with autofilters to only count the entries which are visible (not filtered out with the auto filter).

I would like to get a subtotal for the function:

=COUNTIF(N8:N10853,"MISSING")

but there does not appear to be a function_number for a countif statement.

Is there another way?

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Zack Barresse

##### MrExcel MVP

(untested)

=SUMPRODUCT(SUBTOTAL(3,OFFSET(N8:N10853,ROW(N8:N10853)-ROW(N8),0,1)),--(N8:N10853="MISSING"))

perfect - thanks

#### Zack Barresse

##### MrExcel MVP
You're welcome.

Also, when your data is filtered, if you select it then your StatusBar info (bottom right, right-click to set mathematical operation) will reflect the filtered data as well.

#### syang

##### New Member

(untested)

=SUMPRODUCT(SUBTOTAL(3,OFFSET(N8:N10853,ROW(N8:N10853)-ROW(N8),0,1)),--(N8:N10853="MISSING"))

Thank you for the formula. I was wondering if you could explain why the third cell reference has to be just N8 and not N8:N10853, like the other cell references. I tried it both ways and they both work, but the formula where I make all the cell references N8:N10853 does not always display accurate numbers when I filter my list. I just wanted to know so I can understand Excel better. This is the formula I tried:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(N8:N10853,ROW(N8:N10853)-ROW(N8:N10853),0,1)),--(N8:N10853="MISSING"))

Thanks!

Replies
1
Views
660
Replies
9
Views
264
Replies
2
Views
299
Replies
1
Views
324
Replies
3
Views
362

1,195,600
Messages
6,010,651
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back