I think I need a count formula

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?

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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()))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • Eric Law
    Eric Law ✭✭✭✭✭✭

    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>).

  • Voyage1
    Voyage1 ✭✭

    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.


    Sheet 2: Activity Tracker

    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.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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()))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!