COUNTIFS using two fields

Tmaid ✭✭
edited 07/14/23 in Formulas and Functions

This is my formula

=COUNTIF(([Restack Required]7:[Restack Required]370, "Y"), Site:Site, = "1")

I'm getting the correct response is I have separated out as shown below:

=COUNTIF(Site:Site, "1")- This column is a checkbox

=COUNTIF([Restack Required]7:[Restack Required]370, "Y")

But putting them together I get #UNPARSEABLE


  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    Give this a try:

    =COUNTIFS(Site:Site, "1", [Restack Required]7:[Restack Required]370, "Y")

  • Tmaid
    Tmaid ✭✭
    edited 07/15/23

    it came back with #incorrect argument set

    I had to edit it to this and it worked

    =COUNTIFS(Site7:Site370, "1", [Restack Required]7:[Restack Required]370, "Y")


  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    I didn't even catch it before. Is there a reason you are only looking at rows 7-370 in the second statement?

    Site:Site will look at the entire Site column, while [Restack Required]7:[Restack Required]370 will only look at rows 7-370. Since you are evaluating values in two different columns, they will need to be the same. Also, if you specify row 370, there must be at least 370 rows in the sheet or you will receive an error. You will want one of these options:

    =COUNTIFS([Site]:[Site], "1", [Restack Required]:[Restack Required], "Y")

    =COUNTIFS([Site]7:[Site]370, "1", [Restack Required]7:[Restack Required]370, "Y")

  • Tmaid
    Tmaid ✭✭

    I was hoping to use the full column in the formula but I am creating sums at the bottom of the sheet and right under the "Restack Required" data. So I wanted to limit what it was looking at.

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    The last formula should work for you then. Just modify the row numbers if you need something slightly different. If you really want to use the entire columns, you could always move your sums to a different column.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!