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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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,223,885
Messages
6,175,182
Members
452,615
Latest member
bogeys2birdies

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