Count Comma Separated Values in a Single Cell with a Less Than or Equal to Condition

**poxtea** · 12-07-2015, 07:09 PM

This is my first post and it is for a question that has plagued me for quite a while.
I'm not very tech savvy so I'm struggling a bit.

I have a report where we put multiple number values in one cell, separated by commas (e.x., 32, 14, 3, 55, 107, 1, 0)
If that example is in C4 then C5 will have the same format but with different numbers, (e.x., 5, 88, 4, 0)
This continues for several rows worth of data.
I am also working with two separate sheets, one that has all the general figures (the examples would be on this sheet) and the other is where I am compiling the information I need from the first sheet (where the function I need will be going).

So, ultimately I need to go through these different rows and count how many times a number less than or equal to 14 appears.
I would greatly appreciate any help there is to be had. Thank you.

**Richard Buttrey** · 12-07-2015, 07:25 PM

Hi,

Have you considered using TextToColumns to put all the numbers in individual cells then using a COUNTIF() function?

**Tony Valko** · 12-07-2015, 09:04 PM

I can get a count per cell...

Data Range

	C	D
6	32, 14, 3, 55, 107, 1, 0	4
7	5, 88, 4, 0	3
8	------	------

This formula entered in D4 and copied down:

=SUMPRODUCT(--(--TRIM(MID(SUBSTITUTE(C6,",",REPT(" ",99)),
(99*(ROW(INDIRECT("1:"&LEN(C6)-LEN(SUBSTITUTE(C6,",",""))+1))-1))+1,99))<=14))

Count Comma Separated Values in a Single Cell with a Less Than or Equal to Condition

LinkBack

Thread Tools

Rate This Thread

Display

Count Comma Separated Values in a Single Cell with a Less Than or Equal to Condition

Re: Count Comma Separated Values in a Single Cell with a Less Than or Equal to Condition

Re: Count Comma Separated Values in a Single Cell with a Less Than or Equal to Condition

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to count values in single cell separated by commas

[SOLVED] Using comma separated values in a single cell

Count number appear in single column with comma separated

[SOLVED] Need help finding total time of a tasks listed by comma separated values in a single cell

[SOLVED] How to count values separated by a comma

[SOLVED] Single cell Comma Separated Values to validation list [DIFFICULT QUESTION]

count of comma separated values

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions