Formula to Count Criteria from Multiple Cells

Jackson Pavelka

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.

Lauren Kleitz

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

Mike Wilday

Yes, try this formula

=countifs([Column name1]:[Column Name1], "A", [Column Name 2]:[Column Name 2], OR(@cell = "B", @cell = "C", @cell = "D")

Lauren Kleitz

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

Mike Wilday

Replace Column name1 and Column Name 2 with your column names. And remove the brackets [ ] if your column name is only 1 word.