COUNTIFS, within a month AND not blank

I'm really struggling with countifs in my Sheet Summary. I added a helper column to sparse the month. I want to count if the created date is February, and if two columns containing IDs are not blank.

=COUNTIFS([Month]:[Month], = 2,[Contact ID]:[Contact ID 2],""<>)

I'm getting an #UNPARSEABLE error. I tried the formulas as countif individually and they work, but I can't figure out what I'm doing wrong.

Any assistance would be greatly appreciated!

Answers

  • I figured out two of my problems, this formula worked, but please let me know if there's a better way:

    =(COUNTIFS([Date Agent Processed]:[Date Agent Processed], IFERROR(MONTH(@cell), 0) = 2, [Additional Calabrio Contact ID]:[Additional Calabrio Contact ID], <>"") + (COUNTIFS([Date Agent Processed]:[Date Agent Processed], IFERROR(MONTH(@cell), 0) = 2, [Calabrio Contact ID]:[Calabrio Contact ID], <>"")))

  • AravindGP
    AravindGP ✭✭✭✭✭✭

    Hi @Y Lazor


    You COUNTIFS can hold multiple criteria. Not sure why you would have an IFERROR in the formula. If your objective is to pick up a count of items where Contact ID and Contact ID 2 columns are not blank with the month being 2, you can use this formula.

    =COUNTIFS([Month]:[Month], 2, [Additional Calabrio Contact ID]:[Additional Calabrio Contact ID], <> "", [Calabrio Contact ID]:[Calabrio Contact ID], <>"")

    Thanks,

    Aravind GP| Principal Consultant

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

  • Hi @AravindGP

    It took me a while to get the formula right, but when I did, I realized it was only counting if both ID columns weren't blank. I needed a sum of all non-blank columns.

    I read in another forum that countifs will only allow you to include one column per criteria, which was one of my problems. The other issue was I was using ""<> for blank instead of <>"".

    Is there a better way to write that formula, than what I came up with above?

    I appreciate your assistance!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The only thing I would mention would be that you will get a double count on a row if both columns are blank. To avoid this, simply subtract another COUNTIFS counting when both are blank:

    =(COUNTIFS([Date Agent Processed]:[Date Agent Processed], IFERROR(MONTH(@cell), 0) = 2, [Additional Calabrio Contact ID]:[Additional Calabrio Contact ID], <>"") + COUNTIFS([Date Agent Processed]:[Date Agent Processed], IFERROR(MONTH(@cell), 0) = 2, [Calabrio Contact ID]:[Calabrio Contact ID], <>"")) - COUNTIFS([Date Agent Processed]:[Date Agent Processed], IFERROR(MONTH(@cell), 0) = 2, [Calabrio Contact ID]:[Calabrio Contact ID], <>"", [Additional Calabrio Contact ID]:[Additional Calabrio Contact ID], <>"")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!