COUNTIFS formula for projects archived this year for a contact in a multicontact column

✭✭✭✭✭

I'm trying to write a cross-sheet formula that counts the number of projects one lead (eg, Ben Canada) has archived in a given year. Here is a formula that works if Ben Canada is the only contact:

=COUNTIFS({Rollup Sheet Example-Lead}, CONTAINS("Ben Canada", @cell), {Rollup Sheet Example-Archived Date}, YEAR(@cell) = YEAR(TODAY()))

I've tried to use FIND rather than CONTAINS, but get INVALID DATA TYPE. Archived Date is a date field and Lead is a multicontact field.

Lori Flanigan

Tags:

• ✭✭✭✭✭✭

Is your Archived Date column set to an actual date type column? If so, try this...

=COUNTIFS({Summary Rollup Sheet-Initiative Lead}, FIND("Lori Flanigan", @cell) > 0, {Summary Rollup Sheet-Archived Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

• ✭✭✭✭✭

Yes, the Archived Date column is an actual date type column. And, your changes worked. Thank you! The IFERROR isn't making sense, but I'll search for more information about that part of the formula.

• ✭✭✭✭✭✭

How exactly did you try to use the FIND function? This should work...

=COUNTIFS({Rollup Sheet Example-Lead}, FIND("Ben Canada", @cell) > 0, {Rollup Sheet Example-Archived Date}, YEAR(@cell) = YEAR(TODAY()))

• ✭✭✭✭✭

I had forgotten the greater than sign and 0. However, in my actual sheet (not the example that I originally provided) that has the contacts cell linked from a source sheet, the formula still gives me INVALID DATA TYPE.

Here is the actual rollup sheet.

Here is the formula that returns INVALID DATA TYPE:

=COUNTIFS({Summary Rollup Sheet-Initiative Lead}, FIND("Lori Flanigan", @cell) > 0, {Summary Rollup Sheet-Archived Date}, YEAR(@cell) = YEAR(TODAY()))

• ✭✭✭✭✭✭

Is your Archived Date column set to an actual date type column? If so, try this...

=COUNTIFS({Summary Rollup Sheet-Initiative Lead}, FIND("Lori Flanigan", @cell) > 0, {Summary Rollup Sheet-Archived Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

• ✭✭✭✭✭

Yes, the Archived Date column is an actual date type column. And, your changes worked. Thank you! The IFERROR isn't making sense, but I'll search for more information about that part of the formula.

• ✭✭✭✭✭✭

Basically what is happening is that there are blanks and/or non-date values in that column. For this example, I will just say "blank" but it can be taken to mean either.

If there is a blank, then there is no date for the YEAR function to evaluate. This is what is causing the error. To avoid this, we wrap that first YEAR function evaluation in an IFERROR. If there is an error, then we output zero since there is most likely not going to be the need to count how many entries there are in "year zero". This allows us to effectively exclude the blanks from the calculation which gets rid of the error.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!