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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!