Frequency function with text

[Sukhvinder Singh]

Hi everyone,
below are two columns and I want result from column1 like below here in the RESULT column, I can't use frequency function because values are not strings. Could you please help me here?

COLUMN1	RESULT
SVC-1	2
SVC-1	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
SVC-13	2
SVC-13	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0
FALSE	0

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

COLUMN1	RESULT
SVC-1	2				sorted	count the different
SVC-1	0					items
FALSE	0
FALSE	0
FALSE	0				SVC-1	2
FALSE	0				SVC-1	2
FALSE	0				SVC-13	2
SVC-13	2				SVC-13	2
SVC-13	0				FALSE	7
FALSE	0				FALSE	7
FALSE	0				FALSE	7
					FALSE	7
					FALSE	7
					FALSE	7
					FALSE	7
	or just use a pivot table

<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Sukhvinder Singh]

COLUMN1	RESULT	after Match function =match(range,range,0)	then frequency =frequency(match(range,range,0), match(range,range,0))
SVC-1	2	2	2		sorted	count the different
SVC-1	0	2	0			items
FALSE	0	3	7
FALSE	0	3	0
FALSE	0	3	0		SVC-1	2
FALSE	0	3	0		SVC-1	2
FALSE	0	3	0		SVC-13	2
SVC-13	2	8	2		SVC-13	2
SVC-13	0	8	0		FALSE	7
FALSE	0	3	0		FALSE	7
FALSE	0	3	0		FALSE	7
					FALSE	7
					FALSE	7
					FALSE	7
					FALSE	7
	or just use a pivot table

<tbody>
</tbody>

I can't, I just need a logical formula for this. I can use match function and then frequency function but it'll give me the count of cells which contain false value that I don't

 

[Sukhvinder Singh]

I can't use pivot, I just need a logical formula for this, I can use match function and then frequency, but it'll get the value of cells which contain false that I don't want. You can see below

COLUMN1	RESULT	match	Frequency
SVC-1	2	2	2
SVC-1	0	2	0
FALSE	0	5	5
FALSE	0	5	0
FALSE	0	5	0
SVC-13	2	7	2
SVC-13	0	7	0
FALSE	0	5	0
FALSE	0	5	0

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[AhoyNC]

Maybe - Notice that formula in B3 is different than formula in B2. Formula in B3 would be copied down.

Excel Workbook
	A	B
1	COLUMN1	RESULT
2	SVC-1	2
3	SVC-1	0
4	FALSE	0
5	FALSE	0
6	FALSE	0
7	FALSE	0
8	FALSE	0
9	FALSE	0
10	SVC-13	2
11	SVC-13	0
12	FALSE	0
13	FALSE	0
14	FALSE	0
15	FALSE	0
16	FALSE	0
Sheet

 

[oldbrewer]

COLUMN1

RESULT

SVC-1

2

SVC-1

0

FALSE

0

FALSE

0

FALSE

0

col H

FALSE

0

FALSE

0

SVC-13

2

SVC-1

2

2

ROW 9

SVC-13

0

SVC-1

2

row 10

FALSE

0

#N/A

FALSE

0

#N/A

#N/A

#N/A

SVC-13

3

3

SVC-13

3

SVC-13

3

#N/A

#N/A

the word false is a special word in excel

so I replaced with #n/a

using ifiserror ignores the #n/a's in the list

this formula FOR THE FIRST ITEM IN THE LIST IN COLUMN H

=IF(G9="","",G9)

THIS FORMULA FOR THE SECOND ITEM IN THE LIST, DRAGGED DOWN

COLUMN H OF COURSE

=IF(G10="","",IF(G9="",G10,IF(AND(F10=F9,G10=G9),"",G10)))

<colgroup><col width="64" span="16" style="width:48pt"> </colgroup><tbody>
</tbody>