Count cells containing text string, based on date range and additional condition

[hanns]

	A	B	C	D	E	F	G	H	I
1	Date	Red	Blue	Red	Green	Blue	Red	Green	Green
2	02 Jan 2018	x	x	x, y		x	y		x
3	11 Jan 2018	x, z						x
4	24 Jan 2018			z	x	x	z		y
5	04 Feb 2018	x, y
6	15 Feb 2018	z		y		x, z		x	x, y
7	07 Mar 2018				x
8	16 Mar 2018	x		y, z		x		z
9	29 Mar 2018	x	x, z			y

<tbody>
</tbody>

Hi,
I could use some help to automate some simple data trending please, example above. I'd like to count the number of cells that contain a specific string of text, for a give date range, for a specific category.

For example, I'd like to find the number of times I've had a "x" result for any "Red" tests during January. So if any cell in column A contains a date in January, I'd like to count the number of corresponding cells in columns B, D and G that contain the text string "x", in this example = 3.

Annoyingly the spreadsheets are a bit of a mess, with dozens of non-contiguous columns of data, and multiple data entries in a single cell (e.g. "x, y, z" all as a string of text, separated by commas).

Any help would be greatly appreciated, thanks

 

[hanns]

I should clarify - I can define the columns ("colour" categories in the example above) to check in manually, that part doesn't need to be automated. I'm just trying to check for a specific date range in the first column, and count instances of a specific text string in the corresponding cells in a few other set columns

 

[gaz_chops]

Try

=SUMPRODUCT((B2:I2=A13)*(A3:A10>=A12)*(A3:A10<=EOMONTH(A12,0))*(ISNUMBER(SEARCH(A14,B3:I10))))

[TABLE="width: 797"]
<colgroup><col width="101" style="width: 76pt;"><col width="87" span="8" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="width: 101"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Date[/TD]
[TD="class: xl67"]Red[/TD]
[TD="class: xl68"]Blue[/TD]
[TD="class: xl67"]Red[/TD]
[TD="class: xl69"]Green[/TD]
[TD="class: xl68"]Blue[/TD]
[TD="class: xl67"]Red[/TD]
[TD="class: xl69"]Green[/TD]
[TD="class: xl69"]Green[/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]02-Jan-18[/TD]
[TD="class: xl63"]x[/TD]
[TD="class: xl63"]x[/TD]
[TD="class: xl63"]x, y[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]x[/TD]
[TD="class: xl63"]y[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]x[/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]11-Jan-18[/TD]
[TD="class: xl63"]x, z[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]x[/TD]
[TD="class: xl63"]z[/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]24-Jan-18[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]z[/TD]
[TD="class: xl63"]x[/TD]
[TD="class: xl63"]x[/TD]
[TD="class: xl63"]z[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]y[/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]04-Feb-18[/TD]
[TD="class: xl63"]x, y[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]15-Feb-18[/TD]
[TD="class: xl63"]z[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]y[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]x, z[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]x[/TD]
[TD="class: xl63"]x, y[/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]07-Mar-18[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]x[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]16-Mar-18[/TD]
[TD="class: xl63"]x[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]y, z[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]x[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]z[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl70, align: right"]29-Mar-18[/TD]
[TD="class: xl63"]x[/TD]
[TD="class: xl63"]x, z[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]y[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71, align: center"]01/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl72, align: center"]green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]z[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[hanns]

That worked perfectly, thanks very much, this has saved me hours of work

 

[gaz_chops]

You're welcome, glad it worked.