Count cells, not occurances, with multiple criteria

[SpadeEXE]

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

8/1/17​	RED, BLUE	RED
8/1/17​	RED, WHITE	WHITE
8/1/17​	BLUE, WHITE	BLUE
8/1/17​	YELLOW
4/1/17​	YELLOW
4/1/17​	BLUE
4/1/17​	WHITE, RED
4/1/17​	YELLOW, WHITE

Sheet: Sheet1

 

[AlanY]

confirm your formula with shift-control- enter together

 

[SpadeEXE]

confirm your formula with shift-control- enter together

I did that and it is unchanged, still counting 1.

 

[Aladin Akyurek]

I did that and it is unchanged, still counting 1.

There is but 1 record that satisfies both 'contains RED' and < TODAY()-60...

8/1/2017	RED, BLUE	RED			6/30/2017
8/1/2017	RED, WHITE	WHITE			RED
8/1/2017	BLUE, WHITE	BLUE			1
8/1/2017	YELLOW
4/1/2017	YELLOW
4/1/2017	BLUE
4/1/2017	WHITE, RED
4/1/2017	YELLOW, WHITE

<colgroup><col><col><col span="3"><col></colgroup><tbody> </tbody>

 

[Marcelo Branco]

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)>

 

[SpadeEXE]

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.

 

[SpadeEXE]

I think I am missing some of your formula.

 

[Marcelo Branco]

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.

 

[SpadeEXE]

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.

 

[Marcelo Branco]

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.