Does COUNTIF work when the first argument range is a formula in a cell?

dojones

Below is the formula. It returns 0. I want to know if it is possible to use a column cells with formula in the range.

=COUNTIF([Same Sheet Formula]:[Same Sheet Formula], [YYMM List Text]@row)

[Same Sheet Formula] = =IFERROR(RIGHT(YEAR(TestDate@row), 2) + "" + IF(LEN(MONTH(TestDate@row)) < 2, "0" + MONTH(TestDate@row), MONTH(TestDate@row)), "")

I've tried different options. It works when [Same Sheet Formula] is entered as a fixed value or simple mathematical formula i.e cell +1. I thought it could be a text vs number situation, but using the criteria as a number or text didn't make a difference.

My intent was to reference a formula on another sheet, but couldn't get it to work so put the formula on same sheet and it doesn't work there either.

Kelly Moore

Hey @dojones

I wonder if you have an extra space or an apostrophe in one column vs your search criteria.

You shouldn't have to bring a formula from one sheet to another - your original cross sheet COUNTIFS should have worked. When facing a text vs numeric value issue, you can often solve the problem by wrapping the criteria with the VALUE function.

Let's simplify and return to your original cross sheet formula and see what is not happening as you originally expected.

Kelly

Kelly Moore

Hey @dojones

I wonder if you have an extra space or an apostrophe in one column vs your search criteria.

You shouldn't have to bring a formula from one sheet to another - your original cross sheet COUNTIFS should have worked. When facing a text vs numeric value issue, you can often solve the problem by wrapping the criteria with the VALUE function.

Let's simplify and return to your original cross sheet formula and see what is not happening as you originally expected.

Kelly

dojones

Kelly, thanks for your response. I reduced the sheet to a few columns to test. The only thing I could get to work was to wrap the formula (Range) in a Value function like below. I did check the length of both, and they were the same so no extra spaces or apostrophes were there.

=VALUE(IFERROR(RIGHT(YEAR(TestDate@row), 2) + "" + IF(LEN(MONTH(TestDate@row)) < 2, "0" + MONTH(TestDate@row), MONTH(TestDate@row)), ""))

Range is where the formula was referenced.

COUNTIF(range,criterion)

=COUNTIF(([Same Sheet Formula]:[Same Sheet Formula]), ([YYMM Number]@row))

It didn't matter whether the criteria was text or value. Wrapping the criterion in Value function didn't work. It would only work with the formula wrapped in Value.

It's good to know.

Thanks for your help.