Formula to Count with multiple sheet references

So I am understanding COUNTIFS can only reference one sheet per "=COUNTIFS". I have 3 sheets (Appointments, Install, and Completed Install) that I want to pull data from in a count to get a closing percentage for our sales staff.

I am wanting to figure out the time it takes for a customer to go from scheduled appointment to Sold and develop things like closing percentages etc. How would I do based on a reference like Customer# which is on all sheets?

Basically a formula to look at the appointment date and customer number from "appointment sheet" and look at the sold date and customer# from the "install sheet" and "completed sheet"?

This would allow data to pull for things like closing percentage within 30days, 60days, etc...

Find more posts tagged with

Accepted answers

Andrée Starå

@Travis Ryan

Happy to help!

Here's some more information that hopefully helps.

=
INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row; 
{ColumnWithTheValueToMatchAgainsTheCell}; 0))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Did that help/work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

All comments

Andrée Starå

Hi @Travis Ryan

I hope you're well and safe!

I'd recommend using INDEX/MATCH and so-called helper columns to collect from all sheets to the main and then do the calculations as needed there.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Travis Ryan

https://community.smartsheet.com/discussion/comment/318475#Comment_318475

@Andrée Starå

Thank you for the reply. I will have to plead ignorance unfortunately with index and match formulas. I have been reading and trying to understand them. What helper column information would I pull and how?

I have been trying to pull a column of customer#s where that rows appointment date is this year. I cant get that to work at all. I may not be understanding what helper columns you are talking about.

Andrée Starå

@Travis Ryan

Happy to help!

Here's some more information that hopefully helps.

=
INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row; 
{ColumnWithTheValueToMatchAgainsTheCell}; 0))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Did that help/work?

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.