Counting Text Strings With Conditions
 

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!

Counting Text Strings With Conditions
 

=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

Counting Text Strings With Conditions
 

Think you need sumproduct

Try the below

=SUMPRODUCT(--(D1:D8="Red")*(C1:C8<"delete"))

Link attached for more on Sumproduct

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=561761