Counting criteria from a multiple drop-down column across multiple sheets

lvolpe

Hello,

I'd like to show the number of tasks each person on my team is working on in a roll-up sheet. On all my project sheets, the 'Assigned To' column allows multiple names. When I attempt to aggregate the number of times a name appears in this column on all the referenced sheets, I get an incorrect number (no formula errors, just not the right number). I've attempted multiple formula combinations recommended by pros on the SmartSheet community board, but just can't seem to get it to calculate properly. Here are a few examples of my recent attempts:

=COUNTIFS({Sheet 1 Range 1}, HAS({Sheet 1 Range 1}, "NAME"))+COUNTIFS({Sheet 2 Range 1}, HAS({Sheet 2 Range 1}, "NAME"))

=COUNTIFS({Sheet 1 Range 1}, CONTAINS({Sheet 1 Range 1},"NAME"))+COUNTIFS({Sheet 2 Range 1}, CONTAINS({Sheet 2 Range 1}, "NAME"))

=COUNTIFS({Sheet 1 Range 1}, CONTAINS("NAME", @cell))+COUNTIFS({Sheet 2 Range 1}, CONTAINS("NAME", @cell))

I'm stumped! Can you help me troubleshoot this?

ron.judenberg112096

Try

=COUNTIFS({Sheet 1 Range 1}, HAS(@cell, "NAME")) + COUNTIFS({Sheet 2 Range 1}, HAS(@cell, "NAME"))

 

ron.judenberg112096

Try

=COUNTIFS({Sheet 1 Range 1}, HAS(@cell, "NAME")) + COUNTIFS({Sheet 2 Range 1}, HAS(@cell, "NAME"))

 

lvolpe

Thanks! That works!