Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Countifs formula for distinct values
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.
Answers
-
-
What would that formula be? I have tried and it keeps comings up with the incorrect error.
-
Can you copy/paste the formula that is causing the error from the sheet to here?
-
=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.
-
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?
-
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.
-
What about the actual data that is giving you all ones?
-
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"
-
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.
-
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
