What formula to use to calculate a column with a drop down in another sheet by month.

Hi, I have created a tracking sheet that contains data and issues brought up each month. Now I am wanting to take data from specific columns and have a formula that counts the entries by month in another sheet. I have tried a few different formulas and have been getting errors.

The column I am wanting to calculate has 4 selections, Class 1, Class 2, Class 3 and Class 4. I am wanting to know the total of each at the end of each month.

I would like the total to appear in the below.

Pulled from the below sheet/column

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 04/14/22 Answer ✓

    That certainly looks like a date format to me...

    But I figured out your issue!! - blank cells in your date column! It can't evaluate a blank value with the MONTH function. So here's what we do to get around that...

    First, how does COUNTIFS really work? COUNTIFS formula evaluates its criteria from left to right, and reduces which cells it considers as it evaluates each criteria. So we're first going to tell it to find the cells that are date values, and from those, find the ones that match the proper month number, and from those, count the ones with the right issue:

    =COUNTIFS({QA Alert test Sheet Range 3}, ISDATE(@cell), {QA Alert test Sheet Range 3}, MONTH(@cell) = 4, {QA Alert test Sheet Range 2}, "Class 1 - Missing Parts")



    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!