COUNTIFS, within a month AND not blank

Y Lazor · 03/11/24

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!

Y Lazor · 03/11/24

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 · 03/12/24

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], <>"")

Y Lazor · 03/13/24

https://community.smartsheet.com/discussion/comment/424904#Comment_424904

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 · 03/13/24

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], <>"")

COUNTIFS, within a month AND not blank

Answers

Help Article Resources

Categories