I think I need a count formula

Options
Voyage1
Voyage1 ✭✭
in Formulas and Functions

Hi,

So I have one sheet that gathers all the form submissions - reporting hours daily for different projects. So this is going to be multiple entries from the same people.

The second sheet is a list of the members.

What I want to do is count how many submissions were made by each member within a week so I can follow up with the members who haven't made entries yet. This would be (hopefully) reported on the second sheet so I can have a status showing for which member needs to submit a form.


Currently, I was just vlookup the hour column and if it was = 0 or it would be green. If no entries found then it would be red. But like I said, this only counts the first submission and the modified date column (was trying to get it to act like a timestamp to change) updates all the rows when 1 row is updated.

So to recap. Sheet 1 collects the hours per day. We are looking to have members submit on a weekly basis. So there may be up to 5 (maybe more entries) per person a week.

Sheet 2 identifies the person and I currently have it to show 1 entry but will not show all entries.

So my automation is if a person on sheet two has a red status by Friday @11 based off the date range of the modified date column of that week, they will get an alert requesting an update via form.


So I need to be able to see how many entries a person makes on sheet 2 from sheet 1 weekly.


That possible?

· Share on FacebookShare on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Voyage1

    Yes! You can include more parameters into a COUNTIF formula if you make it plural with COUNTIFS

    In regards to the "Week" that you're looking for, is this a Monday-Friday week, or could we use the WEEKNUMBER Function?

    For example:

    =COUNTIFS({Hours Submission range 1}, [ID #]@row, {Date Created column}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()))

    · Share on FacebookShare on Twitter

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide some screenshots with sample data for reference?

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Options

    Hello @Voyage1,

    Have you tried a COUNTIF with their ID? On sheet 1, next to their name, or whatever, do =COUNTIF(Name:Name <Reference Sheet 1's Name Column>, =Name@Row <Reference the Name in Sheet 2>).

    · Share on FacebookShare on Twitter
  • Voyage1
    Voyage1 ✭✭
    Options

    ok. Let's try with visuals. I think I get where you are going with it, just can't seem to get the formula right.

    Sheet 1 (with the form): Hours Submission

    When filling out the form, they just put in their id number and I use a vlookup to sheet 2 to pull their name and group into sheet 1.

    image.png


    Sheet 2: Activity Tracker

    image.png

    so for Hours Entries I currently have

    =IFERROR(VLOOKUP([ID #]@row, {Hours Submission 1}, 2, false), "Need update")


    But what I really need is a count of submissions every week by each individual.

    So if there are no submissions I know who to follow up with.


    So you were saying =COUNTIF(ID:ID<Hours Submission>,=[ID #]@row <Activity Tracker>)

    So =COUNTIF({Hours Submission range 1}, =[ID #]@row).


    think it works.

    Is there a way to include the date also within the same formula - because the modified date column updates all the rows and I want to see who hasn't been doing it.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Voyage1

    Yes! You can include more parameters into a COUNTIF formula if you make it plural with COUNTIFS

    In regards to the "Week" that you're looking for, is this a Monday-Friday week, or could we use the WEEKNUMBER Function?

    For example:

    =COUNTIFS({Hours Submission range 1}, [ID #]@row, {Date Created column}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()))

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!