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!