Does COUNTIF work when the first argument range is a formula in a cell?
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.
Best Answer

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
Answers

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, 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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 361 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!