Countifs - Multiple Unique Values

[tobermory]

Hello, I attach a link with a sample of my spreadsheet as I cannot attach any files on here.

What I'm looking to do in column G is a COUNTIF formula of how many unique fruits (in Column A) are in each box (in Column C).

Thank you in advance for any replies.

https://drive.google.com/open?id=1oHFR8dx88q7ituITylYE2fBfilZcLp4QianB9U7s5fY

 

[barry houdini]

Hello tobermory,

Do you want a formula that works in google sheets or in Excel?

 

[tobermory]

Hi barry, thanks for replying.

Excel please

I do have another request, if anyone could help.

In column D, could I ask some sort of statement to ask if Box X has MORE than one unique item.

So for instance, cell D5 would return YES (as there is 3 different items in box 1), D21 would return NO, (as there is only 1 item in box 2) etc.

Many Thanks

 

[Aladin Akyurek]

You can post such a sample directly here for perusal...

Item		Box			Container	How many different fruits in each box
					Box 1	3
					Box 2	1
					Box 3	6
Orange		Box 1
Orange		Box 1
Orange		Box 1
Banana		Box 1
Banana		Box 1
Banana		Box 1
Banana		Box 1
Banana		Box 1
Banana		Box 1
Strawberry		Box 1
Strawberry		Box 1
Strawberry		Box 1
Strawberry		Box 1
Strawberry		Box 1
Strawberry		Box 1
Orange		Box 2
Orange		Box 2
Orange		Box 2
Orange		Box 2
Orange		Box 2
Orange		Box 3
Banana		Box 3
Strawberry		Box 3
Apple		Box 3
Grape		Box 3
Pear		Box 3

<colgroup><col><col><col span="3"><col><col></colgroup><tbody>
</tbody>

Google Sheets:

In G1 just enter and copy down:

=ARRAYFORMULA(sum(if(frequency(IF(1-($A$5:$A$32=""),if($C$5:$C$32=F2,match($A$5:$A$32,$A$5:$A$32,0))),row($A$5:$A$32)-row($A$5)+1),1)))

In Excel:

Control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(1-($A$5:$A$32=""),IF($C$5:$C$32=F2,MATCH($A$5:$A$32,$A$5:$A$32,0))),ROW($A$5:$A$32)-ROW($A$5)+1),1))

 

[tobermory]

Thank you very very much Aladin Akyurek, very much appreciated sir. Sincere thanks.

Could anyone help with my additional request in post 3 (as below).

Thank you.

In column D, could I ask some sort of statement to ask if Box X has MORE than one unique item.

So for instance, cell D5 would return YES (as there is 3 different items in box 1), D21 would return NO, (as there is only 1 item in box 2) etc.