Countifs in multi contact column referencing another sheet - Help!!

Please help, i am trying to count the total times each name appears in a multi contact column within 365 days referencing another sheet… where have i gone wrong?

=COUNTIFS({Archive T&F Date}, <=TODAY(), {Archive T&F Date}, >=TODAY(-365), {Archive T&F Presenter}, FIND("Jen McFarland", @cell) > 0))

It seems like the @cell bit would work if i were referencing the sheet i were in but i'm really stuck!

Note, my previous formula worked for all cells where only 1 name was listed but has not counted and where more than one name was listed.

=COUNTIFS({Archive T&F Date}, <=TODAY(), {Archive T&F Date}, >=TODAY(-365), {Archive T&F Presenter}, "Jen McFarland")

Find more posts tagged with

Accepted answers

Carson Penticuff

Give this a try:

=COUNTIFS({Archive T&F Date}, <=TODAY(), {Archive T&F Date}, >=TODAY(-365), {Archive T&F Presenter}, HAS(@cell, "Jen McFarland"))

All comments

Carson Penticuff

Give this a try:

=COUNTIFS({Archive T&F Date}, <=TODAY(), {Archive T&F Date}, >=TODAY(-365), {Archive T&F Presenter}, HAS(@cell, "Jen McFarland"))

NHS Cornwall

This seems to be working perfectly, thank you! I shall double check a few totals just to be sure but fingers crossed! Thanks so much.

Carson Penticuff

Awesome, I'm glad it is working for you!

NHS Cornwall

So have another question in relation to this… I now need to add another criteria to my formula and have tried every combination i can think of but either get imparseable or incorrect argument. This bit working perfectly:-

=COUNTIFS({Archive T&F Date}, <=TODAY(), {Archive T&F Date}, >=TODAY(-365), {Archive T&F Presenter}, HAS(@cell, "Jen McFarland"))

The bit i want to add is

{Cancelled}, =0,

Have tried with/without =, in quotation marks, various combinations of false/N/0 but can't get it to work… help!

(Cancelled is the sheet reference to a column in a separate sheet with a tickbox, so i only want it to count the ones that are not ticked)

Carson Penticuff

There could be some text/number inconsistency in your source sheet. Try wrapping the source in the VALUE() tag to force it to be treated as a number.

=COUNTIFS({Archive T&F Date}, <=TODAY(), {Archive T&F Date}, >=TODAY(-365), {Archive T&F Presenter}, HAS(@cell, "Jen McFarland"), {Cancelled}, VALUE(@cell) = 0)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.