# Count filtered cells with exclusion

#### seroberts242

##### New Member
Hi,

I want to count filtered cells but I don't want the count to include cells that contain "x" or empty cells.

I use this formula to count filtered cells:
=SUBTOTAL(103,A2:A140)

I use this to count with the exception of cells that contain "x":
=COUNTIFS(A2:A140,"<>",A2:A140,"<>x")

I guess I'm trying to figure out how to get all of this into one formula..

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### Jorge_B

##### New Member
Hi,
You could try this.

=SUMPRODUCT((A2:A140<>"x")*(A2:A140<>""))

Hope it helps.

#### seroberts242

##### New Member
Thanks for your reply; unfortunately this isn't working for me. Not sure what's happening but the returned counts don't make sense.

For example I have filters on and applied, there are a total of 23 rows after filtering.

Column Q has 3 cells with text and the rest are blank. The count returned is 8.

Another column, O, has 8 cells with text and the rest are filled with "x". The count returned here is 21.

#### XOR LX

##### Well-known Member
Hi.

=SUMPRODUCT(N(SUBTOTAL(3,OFFSET(A2,ROW(A2:A140)-MIN(ROW(A2:A140)),))),N(A2:A140<>""),N(A2:A140<>"x"))

Regards

#### seroberts242

##### New Member
Perfect, thank you XOR LX!

#### XOR LX

##### Well-known Member
Sure! You're welcome!

Replies
1
Views
425
Replies
2
Views
309
Replies
0
Views
615
Replies
4
Views
492
Replies
2
Views
252

1,195,663
Messages
6,011,013
Members
441,579
Latest member
satishrazdhan

### 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