Referencing Content within a Cell

Sarah_Ko

Hi there!

I'm creating a set of formulas that will reference a sign up form. Since there is a lot of Wordy items to copy, I have created a reference column on my metrics page- is there a way to use my count if formula to reference the content in my Metric Reference Column to the column of the Sign Up Form?

I've been trying to use =countifs(Sign up Column 1, $Metric Reference Cell etc.) but can't seem to get it to work- what am I doing wrong?

Thanks!

Paul Newcome

Yes. In place of "specific text" you would use [Column Name]@row.

=COUNTIFS({Range}, "specific text")

would change to

=COUNTIFS{Range}, [Column Name]@row)

David Tutwiler

I'm not sure I understand the problem. Could you post a screenshot with the sensitive data taken out?

Sarah_Ko

Sure:

the view below is the Metrics page (image 1) :

Since I'm doing this for 4 quarters, with 3 sessions per quarter, I didn't want to have to change everything in every formula. For example: Can I reference what is in the cells (under Portfolio, since they are copied from the sign up sheet, in my count if formula that is referencing the column from the sign up form source sheet (image 2 below)

The sign up forms look like this:

My current formula for Portfolios:

=countifs(Sign Up form- Portfolio, "Dropdown value", Session Column, "Session date")

If there's a way to shorten this so I don't have to change the date for every portfolio or type every portfolio name out, that would be great.

hope that helps clarify a bit.

Thanks!

Paul Newcome

Yes. In place of "specific text" you would use [Column Name]@row.

=COUNTIFS({Range}, "specific text")

would change to

=COUNTIFS{Range}, [Column Name]@row)

Sarah_Ko

Great!! That's super simple and helpful.

Thank you!!

Paul Newcome

Happy to help. 👍️