I think I need a count formula

Voyage1

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?

Genevieve P.

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

Paul Newcome

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

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

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.

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