=sumproduct(--(d1:d999="red"),--(c1:c999<"delete"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

wrote:

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!

--

Dave Peterson