Extracting key words from spreadsheet

[Joshaldo]

I just exported a large amount of data from our client management system and I want to report on how many clients pay a particular fee. On the spreadsheet there is text Is there any way to filter and add up how many times this has occurred?

 

[Peter_SSs]

Is there any way

Most probably, but we have no idea what your data is actually like or how it is laid out. What about you give us a small, representative set of sample dummy data (& the expected results) that we can copy and paste to our sheets to test solutions? My signature block below has help on how you can do that.

 

[Joshaldo]

Thank you so much for the reply, much appreciated. The two variables I am wanting to capture are HCP case management Level 1 HIGH or LOW and then have a tally for the total of 210 clients. For example I would like it to display 50 HCP case management Level 1 HIGH and 160 HCP case management Level 1 LOW.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
4															Package Management Detail Report
5	Print Date: 19/11/2019																																			User:
6	Print Time: 8:42 am																																					Page: 1
7	Report	Options:	Filters	-	Date	Range	used	to	determine	Billing	Periods(01/10/2019	to		31/10/2019),		Billing	Period	Date		Range	used		for	calculating	report(01/10/2019			to	30/10/2019),		Multiple		Funders	Selected,	Display		Options	-	All
8	Clients
9
10
11															Total																Active			Claimable		Claimable	Package
12	Package	Client							Aged	Care
13									User ID	Recipient ID		Subsidy		Rate	CC Rate	Leave															Days			Contribution		Subsidy	Episode
14	HCPL1	Bloggs,			John										$24.07	$0.00															1			0		1	M0000004878
15
16																														Direct		Indirect			Other
17									Episode			Start Date	End Date			Transactions									Visits		Units			Units		Units			Units		Rate		Amount
18									M0000004878			31/10/2019						Administration		Fees		Level	1				1.00										2.50		2.50
19																								HCP Case Management Level 1 HIGH			1.00										3.00		3.00
20																				No Management Fee							2.00			2.00
21																												Visits	and	Expenses									$0.00
22																														Case Management									$3.00
23																											Admin												$2.50
24																												Total		Transactions									$5.50
25																														Funding Allocation									$24.07
26																															Consumer Contribution								$0.00
27																															Total Funding Allocation								$24.07
28																														Over/Under	Including			Contingency					$18.57
29																														Closing Balance									$18.57
Sheet1

 

[Peter_SSs]

have a tally for the total of 210 clients. For example I would like it to display 50 HCP case management Level 1 HIGH and 160 HCP case management Level 1 LOW.

I can't see where in your sample data those results come from. Can you clarify?

BTW, you can hide any irrelevant columns before using XL2BB to keep the screen shot smaller.

 

[Joshaldo]

Sorry, it was cell T20. Each client has that data.

 

[Peter_SSs]

Sorry, it was cell T20. Each client has that data.

I'm afraid that doesn't make anything clearer to me. T2 says "No Management Fee". How do you get 50 and 120 out of that?

Is "HCP Case Management Level 1 HIGH" always in column X?

If there are (presumably) multiple rows with "HCP Case Management Level 1 HIGH", are all the cells that say that in the same column as each other?

If I can't copy or build a sheet that accurately reflects what you are dealing with, I can't begin to construct a useful suggestion for getting the result(s) that you want.

 

[Joshaldo]

Ooops, it is in X19. Each of the 210 clients have either "HCP Case Management Level 1 HIGH or LOW". Unfortunately they are NOT in the same column. If it isn't possible I will just need to manually add each one I guess.

:

 

[Peter_SSs]

Still, how do we get the 50 for "HCP Case Management Level 1 HIGH"

1. Is there just 50 such cells somewhere in the sheet and we are counting them?, or
2. Are we summing numbers on the same row? (like cell AA19) (If so, is that number cell always 3 cells to the right of "HCP Case Management Level 1 HIGH"?)
3. Something else?

 

[Joshaldo]

Correct, there are approximately 50 cells there with "HCP Case Management Level 1 HIGH", I am wanting to find exactly how many.

 

[Peter_SSs]

So, say your formula is in column A somewhere, try something like this (adjust range to ensure you will always capture your data range)

=COUNTIF(B1:BZ1000,"HCP Case Management Level 1 HIGH")