Count unique values if a criteria is met

Dylan G

Hi @Genevieve P. ,

I am also working in a sheet summary and your formula was very helpful. I am trying to add a second criteria to further refine my results and have been unsuccessful.

I have three columns [SOW Group] [SOW Paragraph] and [Status]. I would like to count unique values in the [SOW Paragraph] column where the [SOW Paragraph] Column value is "Group A", and the [Status] Column value is "In Progress".

I tried adding a second criterion range and criterion and it returns the value 1. In fact I don't have anything "In Progress" in group A, but changing a group A item to "In Progress" didn't change the result.

Any ideas how I can do this? Thanks!

Genevieve P.

Hi @Dylan G

Are any of your columns multi-select? For example, if your "SOW Group" column is Multi Select, you'll want to use the HAS function to see if a cell has this option selected among others.

Try:

=COUNT(DISTINCT(COLLECT([SOW Paragraph]:[SOW Paragraph], [SOW Group]:[SOW Group], HAS(@cell, "Group A"), Status:Status, "In Progress")))

I also noticed you have the [SOW Paragraph] column repeated. You only need to have it once, at the beginning of the COLLECT function. If you don't have any multi-select columns, try removing the Green reference in your formula above:

=COUNT(DISTINCT(COLLECT([SOW Paragraph]:[SOW Paragraph], [SOW Group]:[SOW Group], "Group A", Status:Status, "In Progress")))

Cheers!

Genevieve

Dylan G

Thank you @Genevieve P. None of the columns are multi select. The formula you suggested worked. Thank you!

=COUNT(DISTINCT(COLLECT([SOW Paragraph]:[SOW Paragraph], [SOW Group]:[SOW Group], "Group A", Status:Status, "In Progress")))

egrillasca

Hello @Genevieve P.

I'm trying to count how many passes within June we got, either on substantial or final milestones. This project manager has 12 but 1 case is duplicated, so actually there are 11 passes

I'm using this formula but I keep getting "1" instead of 11. Any thoughts? (range 3 equals "inspection result" column)

Appreciate your help!!

Genevieve P.

Hey @egrillasca

From a first scan I can't see anything specifically wrong... so let's try changing up how we're looking for your criteria.

• Instead of saying what the Milestone does not equal, why don't we try looking for if it DOES equal your two values.
• For the Month range, let's look for a specific Month instead of a date range

=COUNT(DISTINCT(COLLECT({case id}, {Insp Created}, IFERROR(MONTH(@cell), 0) = 6, {MILESTONE}, OR(@cell = "Final", @cell = "Substantial/Finishes"), {08 Quality Control Tracker Range 3}, "Pass")))

Does this provide you with the correct number?

If not, double check all the {ranges} are pointing to the correct columns:

• {case id} = Case ID column
• {Insp Created} = Date column
• {MILESTONE} = Milestone column
• {08 Quality Control Tracker Range 3} = Inspection Result column

Cheers,

Genevieve

egrillasca

@Genevieve P.

Thank you for your quick answer!!

I keep getting 1 🙃 I checked all the ranges and they are ok.

Genevieve P.

Hey @egrillasca

Can you post a screen capture of the one source sheet (blocking out sensitive data), identifying each of the columns in your ranges and what column type they're set as?

egrillasca

@Genevieve P.

I changed the column type on the dropdown list to text/number and it worked. The thing is I need those two columns to be a dropdown, do I need to created helper columns and use those on my formula?

Text/Number:

Case ID

Date:

Created Date

Dropdown list:

Inspection Result

Genevieve P.

Hi @egrillasca

The columns being dropdown or text shouldn't matter, as long as the values are correctly matching across sheets.

Can you try changing them back to being dropdown? Does it now work with the correct columns?