COUNTIF cell contains CONT'D

[NDHICKEY]

After using the equation provided below by the wonderfu
Harlan Grove, I am able to count all of the cells that contain
specifc number.

OK, here's the additional dilema: I need to sort those by type, whic
is determined by data in the previous column. I've attempted counti
but have not been able to successully merge it with the sumproduc
function.


Say I'm collecting data on the colors of Cars and Trucks, cell A1
"Car" and cell B1 = "red, blue, green"; cell A2 = "Truck" and cell B2
"blue, orange, yellow"; cell C1 "Car" and cell C2 "blue, black, white
and so on... I would like to count how many blue cars I have...

previous posting is attached below, any suggestions??

-*
NDHICKEY < wrote...

Any assistance is greatly appreciated. I have a column of cells which
contain text such as: "1,6,7,23" or "15,21,22" etc.

I want to count all of the cells in the column that contain th number
15, the numer 16, 23, and so on. It is not possible for me to break
out the numbers into separate cells.

...-*

First, file attachments are almost always unnecessary.

If there were only decimal numerals and commas and nothing else i
these text
cells, and if there were always commas between numbers, and if th
number sought
in these cells were N (which could be a defined name for the cell i
which you
enter the number), then try
*
=SUMPRODUCT(--ISNUMBER(FIND(","&N&",",","&Range&",")))

 

[Harlan Grove]

...
...

previous posting is attached below, any suggestions??

...

Two suggestions. First, DON'T START NEW THREADS! Follow-up in the *ORIGINAL*
thread. Second, post a *small*, plain text sample of your data and the your
desired results in your desired layout.

 

[Bob Phillips]

Surely, it's the same formula, juts put the colour in the cell named N

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

After using the equation provided below by the wonderful
Harlan Grove, I am able to count all of the cells that contain a
specifc number.

OK, here's the additional dilema: I need to sort those by type, which
is determined by data in the previous column. I've attempted countif
but have not been able to successully merge it with the sumproduct
function.


Say I'm collecting data on the colors of Cars and Trucks, cell A1 =
"Car" and cell B1 = "red, blue, green"; cell A2 = "Truck" and cell B2 =
"blue, orange, yellow"; cell C1 "Car" and cell C2 "blue, black, white"
and so on... I would like to count how many blue cars I have...

previous posting is attached below, any suggestions??

-*
NDHICKEY < wrote...

Any assistance is greatly appreciated. I have a column of cells which
contain text such as: "1,6,7,23" or "15,21,22" etc.

I want to count all of the cells in the column that contain the number
15, the numer 16, 23, and so on. It is not possible for me to break
out the numbers into separate cells.

..-*

First, file attachments are almost always unnecessary.

If there were only decimal numerals and commas and nothing else in
these text
cells, and if there were always commas between numbers, and if the
number sought
in these cells were N (which could be a defined name for the cell in
which you
enter the number), then try
*
=SUMPRODUCT(--ISNUMBER(FIND(","&N&",",","&Range&","))) *

 

[NDHICKEY]

Below is the type of data I'm working with. I'm analyzing calls for
call center. The goal is to be able to see how many times "2" occur
in the column, and then to see how many time "2" occurs for each typ
of call.

SOURCE DATA:

Type: Score:
PP 1,2,3
PL 4,5,6
PA 1,2,3
IQ 4,5,6
PP 1,2,3
PA 4,5,6
PL 1,2,3
IQ 4,5,6

DESIRED RESULT:
OCCURRENCES by Type of Call
Score # PA PL PP IQ
1 1 1 2 0
2 1 1 2 0
3 1 1 2 0
4 1 1 0 2
5 1 1 0 2
6 1 1 0 2


Thanks so much

 

[Harlan Grove]

Below is the type of data I'm working with. . . . ...
SOURCE DATA:

Type: Score:
PP 1,2,3
PL 4,5,6
PA 1,2,3
IQ 4,5,6
PP 1,2,3
PA 4,5,6
PL 1,2,3
IQ 4,5,6

DESIRED RESULT:
OCCURRENCES by Type of Call
Score # PA PL PP IQ
1 1 1 2 0
2 1 1 2 0
3 1 1 2 0
4 1 1 0 2
5 1 1 0 2
6 1 1 0 2

If your original data range without the top row of column headings were named
TBL, if the top-left cell of your result range (the one containing 'Score #')
were K1, and if you had already entered the top row and leftmost column, try

L2:
=SUMPRODUCT((INDEX(TBL,0,1)=L$1)*ISNUMBER(FIND(","&$K2&",",
","&INDEX(TBL,0,2)&",")))

Fill L2 right into M2:O2, then fill L2:O2 down into L3:O7.