Count cells, not occurances, with multiple criteria

SpadeEXE

New Member
Joined
Aug 28, 2017
Messages
6
I'm trying to count the number of cells in column B (B1:B8) that contain one of a couple of words from a list (C1:C3), not occurances. Also, I only want the cells counted if the dates next to them in column A are older than Today()-60. The cells in column B sometimes contain more than one word from the list. I have my formula in C5 and what I've come up with is =COUNTIFS(A1:A8,"<"&TODAY()-60,B1:B8,{"*RED*","*WHITE*","*BLUE*"}), but it's only counting occurances of "*RED*". It counts 1, but I need it to count 3. I'm stuck. I'd like to do this without using a hidden column or cells. Thanks in advance.

Excel 2016 (Mac) 64 bit

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]
8/1/17​
[/td][td]RED, BLUE[/td][td]RED[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
8/1/17​
[/td][td]RED, WHITE[/td][td]WHITE[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
8/1/17​
[/td][td]BLUE, WHITE[/td][td]BLUE[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
8/1/17​
[/td][td]YELLOW[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
4/1/17​
[/td][td]YELLOW[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
4/1/17​
[/td][td]BLUE[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
4/1/17​
[/td][td]WHITE, RED[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
4/1/17​
[/td][td]YELLOW, WHITE[/td][td][/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
confirm your formula with shift-control- enter together
 
Upvote 0
I did that and it is unchanged, still counting 1.
There is but 1 record that satisfies both 'contains RED' and < TODAY()-60...[TABLE="width: 478"]<colgroup><col><col><col span="3"><col></colgroup><tbody>[TR][TD]8/1/2017[/TD][TD]RED, BLUE[/TD][TD]RED[/TD][TD] [/TD][TD] [/TD][TD="align: right"]6/30/2017[/TD][/TR][TR][TD]8/1/2017[/TD][TD]RED, WHITE[/TD][TD]WHITE[/TD][TD] [/TD][TD] [/TD][TD]RED[/TD][/TR][TR][TD]8/1/2017[/TD][TD]BLUE, WHITE[/TD][TD]BLUE[/TD][TD] [/TD][TD] [/TD][TD="align: right"]1[/TD][/TR][TR][TD]8/1/2017[/TD][TD]YELLOW[/TD][TD] [/TD][TD] [/TD][TD] [/TD][TD] [/TD][/TR][TR][TD]4/1/2017[/TD][TD]YELLOW[/TD][TD] [/TD][TD] [/TD][TD] [/TD][TD] [/TD][/TR][TR][TD]4/1/2017[/TD][TD]BLUE[/TD][TD] [/TD][TD] [/TD][TD] [/TD][TD] [/TD][/TR][TR][TD]4/1/2017[/TD][TD]WHITE, RED[/TD][TD] [/TD][TD] [/TD][TD] [/TD][TD] [/TD][/TR][TR][TD]4/1/2017[/TD][TD]YELLOW, WHITE[/TD][TD] [/TD][TD] [/TD][TD] [/TD][TD] [/TD][/TR]</tbody>[/TABLE]
 
Upvote 0
Maybe this array formula
=SUM(IF(A1:A8 < TODAY()-60,IF(MMULT(--ISNUMBER(SEARCH(TRANSPOSE(C1:C3),B1:B8)),ROW(C1:C3)^0)>0,1)))<today()-60,if(mmult(--isnumber(search(transpose(c1:c3),b1:b8)),row(c1:c3)^0)>
confirmed with Ctrl+Shift+Enter, not just Enter

M.</today()-60,if(mmult(--isnumber(search(transpose(c1:c3),b1:b8)),row(c1:c3)^0)>
 
Last edited:
Upvote 0
Correct, but I want to count how many cells contain either RED, WHITE, or BLUE, and is older than 60 days, not just RED. So it should count A6, A7 once (counting cells, not occurances), and A8. Thanks.
 
Upvote 0
I think I am missing some of your formula.

I edited post #5
Remark: Are you using Mac? I have no experience with Mac. To enter an array formula, *maybe*...
Shift + CMD + Enter
Not sure ...

M.
 
Last edited:
Upvote 0
I edited post #5
Remark: I have no experience with Mac. To enter an array formula, *maybe*...
Shift + CMD + Enter
Not sure ...
M.

That is correct, but your post only has the formula pasted halfway through SEARCH... it ends at "B1:B8" unless I'm missing something. Thanks.
 
Upvote 0
One more time

Array formula
=SUM(IF(A1:A8 < TODAY()-60,IF(MMULT(--ISNUMBER(SEARCH(TRANSPOSE(C1:C3),B1:B8)),ROW(C1:C3)^0)>0,1)))
Ctrl + Shift+ Enter

Mac: Shift+CMD+Enter (not sure)

Can you see the whole formula now?

M.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

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