Multiple COUNTIF

[BeeRainbow]

Hi,

I'm trying to use multiple COUNTIF to check two sets of criteria.

I have a list of ID’s who have a specific fruit for each day of the week. What I need is for the ID and fruit columns to be compared, with the following criteria

If duplicate IDs are found and the same fruit on both days it should display ‘Same fruit’
If duplicate IDs are found but the fruits are different for the days then it should display ‘Different fruit’
If no duplicate IDs re found then the actual name of the fruit correlating with that ID should be displayed

My data contains three columns – Days (column A), ID (column B), Fruit (column C) and I’m using the following formula in column D

=IF(COUNTIF(B:B,B2)>1,"Same fruit", "Different fruit")

I tried to add further COUNTIF statement but it results in errors.

Any help with this will be appreciated

Thank you

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(COUNTIFS(B:B,B2)=1,C2,IF(COUNTIFS(B:B,B2)=COUNTIFS(B:B,B2,C:C,C2),"same","Different"))

 

[BeeRainbow]

Hi & welcome to MrExcel.
How about

=IF(COUNTIFS(B:B,B2)=1,C2,IF(COUNTIFS(B:B,B2)=COUNTIFS(B:B,B2,C:C,C2),"same","Different"))

Oh wow! Thank you so much Fluff, it has worked

 

[Fluff]

You're welcome & thanks for the feedback.