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
Check out the Formula Handbook template!