Need a Formula that will return the total number of specific text values as a sum numerical value.
I'm having trouble finding the right formula for this. I've tried a few that haven't worked correctly and need a little help.
I want to reference a column in a different sheet and have it return the total value of cells in that column that equal a specific text value. For example, if the reference column has a drop down with the values "Not Active", "Open", and "Filled" and you want the formula to return the total value for all three in a cell. Also, how would you adjust the formula if you want a different cell to return the total value for a single one of those values.
I've tried to use the JOIN and COLLECT but it's giving me all of the text values that match in the cell.
=JOIN(COLLECT({Column with values to return}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"), ", ")
I used that with my reference sheets and it returned all of the text that equaled (i.e., OpenOpenOpenOpenOpen).
Best Answer
-
Are you trying to count up how many times a specific value appears? If so, you should be able to use the COUNTIF function to do so. Ex. =COUNTIF({Column with values to return}, "Not Active") + COUNTIF {Column with values to return}, "Open") + COUNTIF {Column with values to return}, "Filled")
Answers
-
Are you trying to count up how many times a specific value appears? If so, you should be able to use the COUNTIF function to do so. Ex. =COUNTIF({Column with values to return}, "Not Active") + COUNTIF {Column with values to return}, "Open") + COUNTIF {Column with values to return}, "Filled")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!