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

hanns

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

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

hanns

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

Well-known Member
Joined
Apr 29, 2003
Messages
6,485
Platform
  1. MacOS
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:

hanns

New Member
Joined
Feb 23, 2019
Messages
3
That worked perfectly, thanks very much, this has saved me hours of work
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,488
Messages
5,837,623
Members
430,506
Latest member
TonyIbbs

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
Top