# Countifs formula for distinct values

Options
✭✭✭✭✭

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.

• ✭✭✭✭✭✭
Options

You would need a COUNT/DISTINCT/COLLECT combo.

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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"

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options
• ✭✭✭✭✭✭
Options

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