COUNTIFS formula for projects archived this year for a contact in a multicontact column
I'm trying to write a crosssheet 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 ExampleLead}, CONTAINS("Ben Canada", @cell), {Rollup Sheet ExampleArchived 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.
Thanks for your help!
Lori Flanigan
Best Answers

Is your Archived Date column set to an actual date type column? If so, try this...
=COUNTIFS({Summary Rollup SheetInitiative Lead}, FIND("Lori Flanigan", @cell) > 0, {Summary Rollup SheetArchived 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.
Answers

How exactly did you try to use the FIND function? This should work...
=COUNTIFS({Rollup Sheet ExampleLead}, FIND("Ben Canada", @cell) > 0, {Rollup Sheet ExampleArchived 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 SheetInitiative Lead}, FIND("Lori Flanigan", @cell) > 0, {Summary Rollup SheetArchived Date}, YEAR(@cell) = YEAR(TODAY()))

Is your Archived Date column set to an actual date type column? If so, try this...
=COUNTIFS({Summary Rollup SheetInitiative Lead}, FIND("Lori Flanigan", @cell) > 0, {Summary Rollup SheetArchived 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 nondate 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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 200 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!