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

hanns

New Member
Joined
Feb 23, 2019
Messages
3
[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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
Try

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

Code:
[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]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,226,112
Messages
6,189,039
Members
453,520
Latest member
packrat68

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top