Please help with metric sheet formulas!

I need help with a couple of formulas for my metric sheet.

1- I have a master sheet and am trying to figure out a formula for my metric sheet to list all duplicate names on that sheet.

2- I have built a couple of reports from one master sheet. I need a formula to get a count of how many names are on each report.

3- I have an expiration date column that calculates many expiration dates on that row and returns the earliest date. If that expires, I want to see the name on that row, the representative responsible to follow up (from a dropdown) and which task from that filter expired.

Thank you!!

Find more posts tagged with

Accepted answers

All comments

Brent Wilson

The easiest for Duplicate names is to create a helper column "Duplicates".
1. =COUNTIF(Name:Name, =Name@row) would count the duplicates
2. =IF(COUNTIF(Name:Name, =Name@row)>1, "YES", "NO") would put a Yes or No if that name is a duplicate
3. You can just create a filter on that column and it will only show the duplicates
=COUNT(Name:Name) will give you a Count of the names, including duplicates
1. For multiple Sheets, you would just have to change to a named range =COUNT({SheetOneNames})
2. =COUNT(DISTINCT(Name:Name)) would remove duplicates
  1. =COUNT(DISTINCT({SheetOneNames})) would work from another sheet
For the Last, it is going to involve some INDEX and MATCH functions..
1. Basically, you want to find the row number of the earliest date you are returning.
2. Then you can run some In INDEX functions using the Row Number. to return things like the representative associated with that expiration date.
3. Would probably have to see some more data to provide you a formula

Tzippy

@Brent Wilson

Thank you so much for your help!!

1- Duplicates worked - thank you! I added a countif to my metric sheet to return the number of duplicates on my master sheet.

2- To clarify, I want to add a formula to my metric sheet to see how many names are currently on each report. I cant seem to reference a report in a formula. Only sheets come up as an option.

3- see example below:

When a date in the "upcoming expirations" column is in the past I want to see the "name", "HR Rep" and which task matches that date. (In other words which task has already expired) So in this case I'd want to see:

Name: Jennifer, HR Rep: Cindy, Expired: Availability expiration (maybe we can include the date it expired as well)

there are many more tasks but I just added two for reference.

Thank you so much!

4- One more thing if you have time, How do I set up a VLOOKUP on my master sheet where it lists all the reports that the row is on. In other words I want to know on which report the current row is currently on.

Thank you so so much for your help!!

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.