Help with COUNTIFs

I am trying to count the number of payment dates that exist for a client, so I want to count the number of times a cell in a row is not blank.

The columns are not contiguous-there is another column between these that I have hidden. You can see in the # of Payments Received column that the formula I have entered is not returning correct results. For example, the second row should show a value of 5, but the formula returned a value of 0. Here is the formula I have entered:

=COUNTIFS([December Payment Date]@row, <>"", [February Payment Date]@row, <>"", [January Payment Date]@row, <>"", [March Payment Date]@row, <>"", [April Payment Date]@row, <>"", [May Payment Date]@row, <>"", [June Payment Date]@row, <>"", [July Payment Date]@row, <>"", [August Payment Date]@row, <>"", [September Payment Date]@row, <>"")


What changes should I make to get the calculations I am seeking?

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @LMallery

    This is strange. It seems like a simple thing but what I think is happening is that SmartSheet is having an issue with criteria1 and criteria2 being the exact same thing.

    If I use the formula =COUNTIFS([February Payment Date]@row, <>"") then it returns a 1. However, if I use =COUNTIFS([February Payment Date]@row, <>"", [March Payment Date]@row, <>"") then I still get a 1. It's refusing to review and count anything after the first range1/criteria1 because the subsequent criteria is exactly the same.

    I'll keep poking at it and see if I can find a way to get it to work.

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @LMallery

    Ok. I figured it out by reading the help on the function. The COUNT feature won't count blank cells. That makes the formula easy as this:

    =COUNT([December Payment Date]@row, [January Payment Date]@row, [February Payment Date]@row, [March Payment Date]@row, [April Payment Date]@row, [May Payment Date]@row, [June Payment Date]@row, [July Payment Date]@row, [August Payment Date]@row, [September Payment Date]@row, [October Payment Date]@row, [November Payment Date]@row)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!