Functions- Finding and counting specified text in cell range

I need to identify the correct function to achieve the following task:
Search specified column to find "22087", and count the occurences.

The problem I am having is that the cells within this column often contain
more than one numeric value in each cell. For example, contents of one
single cell may look like this: 22087, 22058, 22064. This cell identifies
the locations of all the jobs a candidate is willing to consider. If a
candidate is willing to consider placement in multiple cities/states, they
are all listed within the cell.
I need to know how many candidates are applying for each location #, and
CountIF only seems to work if the cells contain only one value. Help?

Try:

=SUMPRODUCT(--ISNUMBER(SEARCH("22087",A1:B100)))

HTH

"holliedavis" wrote:

> I need to identify the correct function to achieve the following task:
> Search specified column to find "22087", and count the occurences.
>
> The problem I am having is that the cells within this column often contain
> more than one numeric value in each cell. For example, contents of one
> single cell may look like this: 22087, 22058, 22064. This cell identifies
> the locations of all the jobs a candidate is willing to consider. If a
> candidate is willing to consider placement in multiple cities/states, they
> are all listed within the cell.
> I need to know how many candidates are applying for each location #, and
> CountIF only seems to work if the cells contain only one value. Help?
>
>

Hollie,

Use the array formula (entered uisng Ctrl-Shift-Enter)

=SUM(ISNUMBER(SEARCH(D1,A1:A100))*1)

where D1 contains the location code that you are interested in, and A1:A100 has your list of values.

HTH,
Bernie
MS Excel MVP


"holliedavis" <[email protected]> wrote in message
news:[email protected]...
>I need to identify the correct function to achieve the following task:
> Search specified column to find "22087", and count the occurences.
>
> The problem I am having is that the cells within this column often contain
> more than one numeric value in each cell. For example, contents of one
> single cell may look like this: 22087, 22058, 22064. This cell identifies
> the locations of all the jobs a candidate is willing to consider. If a
> candidate is willing to consider placement in multiple cities/states, they
> are all listed within the cell.
> I need to know how many candidates are applying for each location #, and
> CountIF only seems to work if the cells contain only one value. Help?
>
>

This formula works perfectly... Thank you! One small question, though... I
am searching range G3:G1000 on 4 worsheets for this information. How do I
write the formula to search the same range in multiple sheets?

My formula looks like this so far, but I need to also search sheets 2, 3,
and 4 and end up with a total number.

=SUMPRODUCT(--ISNUMBER(SEARCH("22948",'Sheet 1'!G3:G1003)))

Thanks!!!!

"Toppers" wrote:

> Try:
>
> =SUMPRODUCT(--ISNUMBER(SEARCH("22087",A1:B100)))
>
> HTH
>
> "holliedavis" wrote:
>
> > I need to identify the correct function to achieve the following task:
> > Search specified column to find "22087", and count the occurences.
> >
> > The problem I am having is that the cells within this column often contain
> > more than one numeric value in each cell. For example, contents of one
> > single cell may look like this: 22087, 22058, 22064. This cell identifies
> > the locations of all the jobs a candidate is willing to consider. If a
> > candidate is willing to consider placement in multiple cities/states, they
> > are all listed within the cell.
> > I need to know how many candidates are applying for each location #, and
> > CountIF only seems to work if the cells contain only one value. Help?
> >
> >

You SHOULD be able to just add a + sign to the end of that formula, copy the
formula (not including the = or the newly added +) and change sheet 1 to
sheet 2. Repeat this for sheets 3 and 4.

Without testing, it should look something like:
=SUMPRODUCT(--ISNUMBER(SEARCH("22948",'Sheet
1'!G3:G1003)))+SUMPRODUCT(--ISNUMBER(SEARCH("22948",'Sheet
2'!G3:G1003)))+SUMPRODUCT(--ISNUMBER(SEARCH("22948",'Sheet
3'!G3:G1003)))+SUMPRODUCT(--ISNUMBER(SEARCH("22948",'Sheet 4'!G3:G1003)))

There may be another way of doing this
Actually, I did test this a little by adding sheet 1 - sheet 4 into my
workbook and putting 22948 somewhere in column g of each. I got 4 (which was
correct in my instance.)
--
Kevin Vaughn


"holliedavis" wrote:

> This formula works perfectly... Thank you! One small question, though... I
> am searching range G3:G1000 on 4 worsheets for this information. How do I
> write the formula to search the same range in multiple sheets?
>
> My formula looks like this so far, but I need to also search sheets 2, 3,
> and 4 and end up with a total number.
>
> =SUMPRODUCT(--ISNUMBER(SEARCH("22948",'Sheet 1'!G3:G1003)))
>
> Thanks!!!!
>
> "Toppers" wrote:
>
> > Try:
> >
> > =SUMPRODUCT(--ISNUMBER(SEARCH("22087",A1:B100)))
> >
> > HTH
> >
> > "holliedavis" wrote:
> >
> > > I need to identify the correct function to achieve the following task:
> > > Search specified column to find "22087", and count the occurences.
> > >
> > > The problem I am having is that the cells within this column often contain
> > > more than one numeric value in each cell. For example, contents of one
> > > single cell may look like this: 22087, 22058, 22064. This cell identifies
> > > the locations of all the jobs a candidate is willing to consider. If a
> > > candidate is willing to consider placement in multiple cities/states, they
> > > are all listed within the cell.
> > > I need to know how many candidates are applying for each location #, and
> > > CountIF only seems to work if the cells contain only one value. Help?
> > >
> > >