Countifs formula for distinct values

swaldon2

Hi! I need a formula that counts rows based on multiple criteria but only provides one response per duplicated value.

For Example I want a countif formula to pull one number that looks at if "Owner Name" = "Amtrack" and "Crossing Agreement Status" = "Owner Contacted" to provide only one number, but note that there are 5 rows that have Amtrack as the owner and the agreement status is always the same for all 5 rows. So this number should push out the value of 1.

Paul Newcome

You would need a COUNT/DISTINCT/COLLECT combo.

swaldon2

What would that formula be? I have tried and it keeps comings up with the incorrect error.

Paul Newcome

Can you copy/paste the formula that is causing the error from the sheet to here?

swaldon2

=COUNT(DISTINCT(COLLECT({Railroad Range 3}, $[Value 3]$2, {Railroad Range 2}, [Metric 2]5)))

This should only produce 1 in the "Owner Not Identified" but it is giving 1 for all values.

Paul Newcome

Are you able to show the column names for the screenshot above as well as the source data that shows where the number should be more than 1?

swaldon2

The screenshot is coming from a metric sheet that references the source sheet in the equation. I created dummy data in the source sheet below with the same column titles as the real sheet. So railroad range in the equation above is looking at the owner name in the source sheet and making sure it equals "rail #1" from the metric sheet screenshot above. Railroad range 2 is looking at the crossing agreement status column in the source sheet and making sure it equals "owner not identified" from the metric sheet screenshot above.

Paul Newcome

What about the actual data that is giving you all ones?

swaldon2

I can't show the actual data as its sensitve information. But the table above is an exact replicate. I am just trying to do a countif on the crossisng agreement status so it should show up as 1 in the metric sheet but it should only show up as 1 for a single crossing agreement type not all. Ex: Railroad C is all "owner identified" meaning in the metric chart it should be 0 for everything but "owner identified"

Paul Newcome

What are the column names in your metrics sheet? I can't see those, so I can't see what each cell reference is actually looking at.

swaldon2

Paul Newcome

Try changing the cell reference to the status to "@row" instead of a row number.

=COUNT(DISTINCT(COLLECT({Railroad Range 3}, $[Value 3]$2, {Railroad Range 2}, [Metric 2]@row)))

But if all of the statuses will always be the same in the source sheet, why are you trying to get a count? What would be the expected output from the sample data you last posted?