Formula to Count Criteria from Multiple Cells
Hi there,
I have a reference sheet from which I need to reference two full columns...
I need to write a formula to count the number of records where Column 1 equals A, and Column 2 equals B, C, or D. I have been working with =Countif and =Countifs but have been unable to find a suitable method of writing this formula. It is the need for an OR function that is throwing me off.
Anyone have ideas? Thanks in advance.
Best Answer
-
Hey Jackson!
To make this function work, you are going to want to tap into the @Cell property! Here is some documentation for @cell: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell?_ga=2.16777785.1682561355.1607473967-1997011459.1605629248#cell)
Ultimately, your formula will look something like this:
=COUNTIFS([Column1]:[Column1], "A", [Column2]:[Column2], OR(@cell = "B", @cell = "C", @cell = "D"))
Where Column1 is the column that you are looking for "A" in and Column2 is the one containing B, C, or D.
Let me know if that helps!
-LK
Answers
-
Yes, try this formula
=countifs([Column name1]:[Column Name1], "A", [Column Name 2]:[Column Name 2], OR(@cell = "B", @cell = "C", @cell = "D")
-
Hey Jackson!
To make this function work, you are going to want to tap into the @Cell property! Here is some documentation for @cell: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell?_ga=2.16777785.1682561355.1607473967-1997011459.1605629248#cell)
Ultimately, your formula will look something like this:
=COUNTIFS([Column1]:[Column1], "A", [Column2]:[Column2], OR(@cell = "B", @cell = "C", @cell = "D"))
Where Column1 is the column that you are looking for "A" in and Column2 is the one containing B, C, or D.
Let me know if that helps!
-LK
-
Replace Column name1 and Column Name 2 with your column names. And remove the brackets [ ] if your column name is only 1 word.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!