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!!

[TABLE="class: grid, width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: center"]A[/TD]
[TD="width: 64, align: center"]B[/TD]
[TD="width: 64, align: center"]C[/TD]
[TD="width: 64, align: center"]D[/TD]
[TD="width: 64, align: center"]E[/TD]
[TD="width: 64, align: center"]F[/TD]
[TD="width: 64, align: center"]G[/TD]
[TD="width: 64, align: center"]H
[/TD]
[/TR]
[TR]
[TD]INPUT[/TD]
[TD][/TD]
[TD]FRUIT[/TD]
[TD]COLOR[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Count[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD][/TD]
[TD]APPLE[/TD]
[TD]RED[/TD]
[TD][/TD]
[TD]ORANGE[/TD]
[TD="align: center"] ??
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BANANA[/TD]
[TD][/TD]
[TD]PEACH[/TD]
[TD]ORANGE[/TD]
[TD][/TD]
[TD]RED[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GRAPE[/TD]
[TD][/TD]
[TD]GRAPE[/TD]
[TD]BLUE[/TD]
[TD][/TD]
[TD]GREEN[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MELON[/TD]
[TD][/TD]
[TD]BERRY[/TD]
[TD]BLUE[/TD]
[TD][/TD]
[TD]BLUE[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MELON[/TD]
[TD][/TD]
[TD]MELON[/TD]
[TD]GREEN[/TD]
[TD][/TD]
[TD]YELLOW[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KIWI[/TD]
[TD][/TD]
[TD]KIWI[/TD]
[TD]GREEN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BANANA[/TD]
[TD][/TD]
[TD]BANANA[/TD]
[TD]YELLOW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORANGE[/TD]
[TD][/TD]
[TD]ORANGE[/TD]
[TD]ORANGE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MANGO[/TD]
[TD][/TD]
[TD]MANGO[/TD]
[TD]ORANGE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]STRAW[/TD]
[TD][/TD]
[TD]STRAW[/TD]
[TD]RED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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:


[TABLE="width: 483"]
<colgroup><col span="2"><col><col span="4"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]INPUT[/TD]
[TD][/TD]
[TD]FRUIT[/TD]
[TD]COLOR[/TD]
[TD][/TD]
[TD][/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD][/TD]
[TD]APPLE[/TD]
[TD]RED[/TD]
[TD][/TD]
[TD]ORANGE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BANANA[/TD]
[TD][/TD]
[TD]PEACH[/TD]
[TD]ORANGE[/TD]
[TD][/TD]
[TD]RED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GRAPE[/TD]
[TD][/TD]
[TD]GRAPE[/TD]
[TD]BLUE[/TD]
[TD][/TD]
[TD]GREEN[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]MELON[/TD]
[TD][/TD]
[TD]BERRY[/TD]
[TD]BLUE[/TD]
[TD][/TD]
[TD]BLUE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]MELON[/TD]
[TD][/TD]
[TD]MELON[/TD]
[TD]GREEN[/TD]
[TD][/TD]
[TD]YELLOW[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]KIWI[/TD]
[TD][/TD]
[TD]KIWI[/TD]
[TD]GREEN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BANANA[/TD]
[TD][/TD]
[TD]BANANA[/TD]
[TD]YELLOW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORANGE[/TD]
[TD][/TD]
[TD]ORANGE[/TD]
[TD]ORANGE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MANGO[/TD]
[TD][/TD]
[TD]MANGO[/TD]
[TD]ORANGE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]STRAW[/TD]
[TD][/TD]
[TD]STRAW[/TD]
[TD]RED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PASSION[/TD]
[TD]RED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]RASPERRY[/TD]
[TD]RED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PLUM[/TD]
[TD]BLUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]INPUT[/TD]
[TD][/TD]
[TD]FRUIT[/TD]
[TD]COLOR[/TD]
[TD][/TD]
[TD][/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]APPLE[/TD]
[TD][/TD]
[TD]APPLE[/TD]
[TD]RED[/TD]
[TD][/TD]
[TD]ORANGE[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]BANANA[/TD]
[TD][/TD]
[TD]PEACH[/TD]
[TD]ORANGE[/TD]
[TD][/TD]
[TD]RED[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]GRAPE[/TD]
[TD][/TD]
[TD]GRAPE[/TD]
[TD]BLUE[/TD]
[TD][/TD]
[TD]GREEN[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]MELON[/TD]
[TD][/TD]
[TD]BERRY[/TD]
[TD]BLUE[/TD]
[TD][/TD]
[TD]BLUE[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]MELON[/TD]
[TD][/TD]
[TD]MELON[/TD]
[TD]GREEN[/TD]
[TD][/TD]
[TD]YELLOW[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]KIWI[/TD]
[TD][/TD]
[TD]KIWI[/TD]
[TD]GREEN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]BANANA[/TD]
[TD][/TD]
[TD]BANANA[/TD]
[TD]YELLOW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]ORANGE[/TD]
[TD][/TD]
[TD]ORANGE[/TD]
[TD]ORANGE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]MANGO[/TD]
[TD][/TD]
[TD]MANGO[/TD]
[TD]ORANGE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]STRAW[/TD]
[TD][/TD]
[TD]STRAW[/TD]
[TD]RED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]PASSION[/TD]
[TD]RED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]RASPERRY[/TD]
[TD]RED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]PLUM[/TD]
[TD]BLUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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))