[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Date
[/TD]
[TD]Red
[/TD]
[TD]Blue
[/TD]
[TD]Red
[/TD]
[TD]Green
[/TD]
[TD]Blue
[/TD]
[TD]Red
[/TD]
[TD]Green
[/TD]
[TD]Green
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]02 Jan 2018
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x, y
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD]y
[/TD]
[TD][/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]11 Jan 2018
[/TD]
[TD]x, z
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]24 Jan 2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD]z
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]z
[/TD]
[TD][/TD]
[TD]y
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]04 Feb 2018
[/TD]
[TD]x, y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]15 Feb 2018
[/TD]
[TD]z
[/TD]
[TD][/TD]
[TD]y
[/TD]
[TD][/TD]
[TD]x, z
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD]x, y
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]07 Mar 2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]16 Mar 2018
[/TD]
[TD]x
[/TD]
[TD][/TD]
[TD]y, z
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD]z
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]29 Mar 2018
[/TD]
[TD]x
[/TD]
[TD]x, z
[/TD]
[TD][/TD]
[TD][/TD]
[TD]y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Date
[/TD]
[TD]Red
[/TD]
[TD]Blue
[/TD]
[TD]Red
[/TD]
[TD]Green
[/TD]
[TD]Blue
[/TD]
[TD]Red
[/TD]
[TD]Green
[/TD]
[TD]Green
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]02 Jan 2018
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x, y
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD]y
[/TD]
[TD][/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]11 Jan 2018
[/TD]
[TD]x, z
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]24 Jan 2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD]z
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]z
[/TD]
[TD][/TD]
[TD]y
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]04 Feb 2018
[/TD]
[TD]x, y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]15 Feb 2018
[/TD]
[TD]z
[/TD]
[TD][/TD]
[TD]y
[/TD]
[TD][/TD]
[TD]x, z
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD]x, y
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]07 Mar 2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]16 Mar 2018
[/TD]
[TD]x
[/TD]
[TD][/TD]
[TD]y, z
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD]z
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]29 Mar 2018
[/TD]
[TD]x
[/TD]
[TD]x, z
[/TD]
[TD][/TD]
[TD][/TD]
[TD]y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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