COUNTIF SPECIAL NUMBER

  • Thread starter Thread starter Aron
  • Start date Start date
A

Aron

I am trying to set a formula that will only count a cells in a column if the
cell is occupied w/ social security numbers.

Appreciate any assistance,
Aron
 
Assuming column A holds them like this: 123-45-6789

=SUMPRODUCT(--(LEN(A1:A100)=11),--(NOT(ISERROR(VALUE(LEFT(A1:A100,3))))),--(NOT(ISERROR(VALUE(MID(A1:A100,5,2))))),--(NOT(ISERROR(VALUE(RIGHT(A1:A100,4))))),--(MID(A1:A100,4,1)="-"),--(MID(A1:A100,7,1)="-"))

This will total all that pass the test that the entire entry is 11
characters, the first three characters are numbers, the two in the middle are
numbers, the four on the end are numbers, the fourth character is the dash
and the seventh character is the dash. It won't work if you try to count an
entire column at once, so edit the A1:A100 part to suit your needs, but don't
use A:A. If you've using a format other than 123-45-6789, post a sample, and
we can adjust from there.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Need Countifs Formula Help 0
Excel VBA to move cursor from 1 cell to another, once original cell is filled with data. 3
COUNTIF Cells in Range? 4
COUNT function - # of occurences a number appears in all cells 26
Excel Use COUNTIF across a range of cells with formulas 0
Countif formula 2
count until blank cell 2
Please help with countif formula 4

Back
Top