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

Lori Flanigan

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.

Thanks for your help!

Lori Flanigan

Paul Newcome

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()))

Lori Flanigan

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.

Paul Newcome

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()))

Lori Flanigan

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()))

Paul Newcome

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()))

Lori Flanigan

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.

Paul Newcome

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.