sumproduct formula excluding values from separate column

[Bornready]

how do i create a formula that still uses sumproduct but excludes data if it contains a text in a separate column?

ex: looking for data that fits the criteria apples, oranges, grapes, bananas, but in a separate column, if it's the color yellow for any of my criteria's then i want to exclude its total.

hopefully this makes sense.

 

[FlameRetired]

Please try this.

Book1
	A	B	C
1	Apple	Green	2
2	Banana	Blue
3	Kiwi	Yellow
4	Orange	Beige
5	Peach	Brown
6	Pear	Red
7	Pineapple	Pink
8	Plum	Purple
9	Tomato	Orange
10	Watermelon	Tan
Sheet1

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT((($A$1:$A$10="pear")+($A$1:$A$10="tomato")+($A$1:$A$10="kiwi")),--($B$1:$B$10<>"yellow"))

 

[Bornready]

$B$1:$B$10<

Thanks for the quick response however this still doesn't work. It gives me the "#VALUE!" error

Question what is the purpose of the --?

 

[FlameRetired]

Question what is the purpose of the --?

The "--" coerces the resulting TRUE/FALSE values in the formula into their corresponding values 1/0. Actually any math operation will do that. You will frequently see people add a 0 or multiply by 1. The "--" is called a double unary. It reportedly calculates fastest of all the methods (though I have never tested this).

Note the $B$1:$B$10< should be $B$1:$B$10<> for "does not equal"