Counting Text Strings With Conditions

[JOHNNY]

HELP! I have tried several combinations of IF() functions COUNTIF()
functions, AND(), no

I am trying to find a function or simple solution to the following:


This is a much simplified version of what my spreadsheet looks like.
A B C D
1 apple 15 delete red
2 orange 16 clear red
3 banana 31 f blue
4 mango 2 clear green
5 apple1 15 clear red
6 apple2 16 f red
7 banana1 39 f blue
8 mango1 6 clear green


I need a formula to scan through column D and count the number of
"red"s except for the "red"s that are "delete"d in column C. In the
above example, the solution would be 3.


THANK YOU FOR YOUR HELP!

 

[Max]

One way ..

Assume source data within A29 (as posted)

Input the criteria
in E2: red
in E3: delete

Then just place in say, F2:
=SUMPRODUCT(($D$2:$D$9=E2)*($C$2:$C$9<>E3))
F2 will return the required results, ie 3 for sample source data

Adapt the ranges to suit, but note that we cannot use entire col
references (eg: D, C:C ) in SUMPRODUCT