Count Lookup

[IvanYerk]

I want to take the input cells, lookup their color values, then count the color occurrences. Probably need to use array but it's my achilles heel. Thanks!!

A	B	C	D	E	F	G	H
INPUT		FRUIT	COLOR			Count
APPLE		APPLE	RED		ORANGE	??
BANANA		PEACH	ORANGE		RED
GRAPE		GRAPE	BLUE		GREEN
MELON		BERRY	BLUE		BLUE
MELON		MELON	GREEN		YELLOW
KIWI		KIWI	GREEN
BANANA		BANANA	YELLOW
ORANGE		ORANGE	ORANGE
MANGO		MANGO	ORANGE
STRAW		STRAW	RED

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[MARZIOTULLIO]

Hi

G2=SUMPRODUCT(($A$2:$A$11=$C$2:$C$11)*($D$2:$D$11=F2)) copy down

 

[IvanYerk]

Thanks for the reply, this is my first post so greatly appreciated. Not sure if the answer changes if I add FRUITS.

It appears with your solution, all ranges have to be equal or else I get #N/A.

=SUMPRODUCT(($A$2:$A$11=$C$2:$C$15)*($D$2:$D$15=F2))

So since I have a defined Input range, is there a way to make the formula more dynamic so I don't have to expand the Input range to include blank cells, i.e. A2:A15. Hope this makes sense. Thanks again!!

For example:

A	B	C	D	E	F	G
INPUT		FRUIT	COLOR			Count
APPLE		APPLE	RED		ORANGE
BANANA		PEACH	ORANGE		RED
GRAPE		GRAPE	BLUE		GREEN
MELON		BERRY	BLUE		BLUE
MELON		MELON	GREEN		YELLOW
KIWI		KIWI	GREEN
BANANA		BANANA	YELLOW
ORANGE		ORANGE	ORANGE
MANGO		MANGO	ORANGE
STRAW		STRAW	RED
		PASSION	RED
		RASPERRY	RED
		PLUM	BLUE

<colgroup><col span="2"><col><col span="4"></colgroup><tbody>
</tbody>

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​
1​	INPUT		FRUIT	COLOR			Count
2​	APPLE		APPLE	RED		ORANGE	2​
3​	BANANA		PEACH	ORANGE		RED	2​
4​	GRAPE		GRAPE	BLUE		GREEN	2​
5​	MELON		BERRY	BLUE		BLUE	1​
6​	MELON		MELON	GREEN		YELLOW	1​
7​	KIWI		KIWI	GREEN
8​	BANANA		BANANA	YELLOW
9​	ORANGE		ORANGE	ORANGE
10​	MANGO		MANGO	ORANGE
11​	STRAW		STRAW	RED
12​			PASSION	RED
13​			RASPERRY	RED
14​			PLUM	BLUE

<tbody>
</tbody>

G2=SUMPRODUCT((ISNUMBER(MATCH($C$2:$C$14,$A$2:$A$11,0)))*(ISNUMBER(MATCH($D$2:$D$14,F2,0)))) copy down

 

[IvanYerk]

works like a charm, a thousand thanks!!!!

 

[MARZIOTULLIO]

You are welcome.

 

[Peter_SSs]

Welcome to the MrExcel board!

Note that you can simplify the last part of that formula
=SUMPRODUCT((ISNUMBER(MATCH($C$2:$C$14,$A$2:$A$11,0)))*($D$2:$D$14=F2))