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
Check out the Formula Handbook template!