Question with "Distinct" and "Countifs" or "Count" with "Collect"

Alan Meshaw

I want to find out how many distinct items are in a list matching a condition but not in a similar distinct list of items matching a different criteria. Here's the setup: How many distinct fruits are recorded for 2024 that are not in the distinct list recorded for 2025 (see image)

?

Ray Lindstrom

Hi @Alan Meshaw

You could create a helper column to concatenate your fruit and year. You can hide that column to keep things pretty. Then do a Count Distinct formula against that column.

My Concat column (name yours whatever you like, and adjust the final formula accordingly) is concatenating the fruit and the year:

=Fruit@row + Year@row

Then your formula can look like this which will count uniques in 2025

Arrow pointing at cell with this formula: =COUNT(CONTAINS(2025, DISTINCT(Concat:Concat)))

Hope that helps!

BRgds,
-Ray