Count cells containing keywords with multiple criteria

I would appreciate any help. I have managed to come up with a formula that counts them all based on the keywords I'm looking for in cells, but I'm trying to also include additional criteria including a specific date and a specific product.

So right now it's showing 4 under 4/7/2024 but I need it to show 2 under 4/7/2024, 1 under 3/31 and 1 under 3/24 based on the data provided.

=SUMPRODUCT(IF($C:$C="No",--ISNUMBER(SEARCH($H2, SUBSTITUTE($D:$D,",",""))),""))

That's my currently formula.

How do I fix this so that I can included some additional criteria for it to count?

excelformula.png

Cell I8 formula , Drag right

Formula:

=COUNT(0/(($C$1:$C$8="No")*($A$1:$A$8=I$7)*($B$1:$B$8=$G7)*ISNUMBER(SEARCH($H7,$D$1:$D$8))))

Cell I2 formula , Drag right

Formula:

=COUNT(0/(($C$1:$C$8="No")*($A$1:$A$8=I$1)*ISNUMBER(SEARCH($H2,$D$1:$D$8))))

OFFICE 365
Cell I2 formula

Formula:

=MAP(I1:K1,LAMBDA(x,ROWS(FILTER(A1:D8,((A1:A8=x)*(C1:C8="No")*ISNUMBER(SEARCH(H2,D1:D8)))))))

Cell I8 formula

Formula:

=IFERROR(MAP(I7:K7,LAMBDA(x,ROWS(FILTER(A1:D8,((A1:A8=x)*(C1:C8="No")*(B1:B8=G7)*ISNUMBER(SEARCH(H7,D1:D8))))))),"")

Originally Posted by wk9128

Cell I8 formula , Drag right

Formula:

=COUNT(0/(($C$1:$C$8="No")*($A$1:$A$8=I$7)*($B$1:$B$8=$G7)*ISNUMBER(SEARCH($H7,$D$1:$D$8))))

Cell I2 formula , Drag right

Formula:

=COUNT(0/(($C$1:$C$8="No")*($A$1:$A$8=I$1)*ISNUMBER(SEARCH($H2,$D$1:$D$8))))

The one for I2 works, but the one for I8 results in 0 I mean.

Originally Posted by Kailef

The one for I2 works, but the one for I8 results in 0 I mean.

Do you want to know the reason? Because your cell B7 content is Produce 1, not Product 1

CELL K2 does not consider dates, resulting in 4

{=COUNT (0/($C $1: $C $8="No") * ISNUMBER (SEARCH ($H2, $D $1: $D $8)))}