Need a Formula that will return the total number of specific text values as a sum numerical value.

SNolan16

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).

Cybulski

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")

Cybulski

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")