Count number of audit by person every week

[BadFish523]

I have a sheet that collects information about audits. I want to build a graph that shows number of audits done by each person but only for the current week. So the sheet collects who did each audit everytime its entered. I would like the graph to count the names by the current week so I can see who is doing how many audit every week. After the week is over the graph would then start over for the next week. Is this eve possible?

Edit: Added info, Dates are in column A and names are in column B if this helps any.

Thanks

 

[JPARKHURST]

=countIFS(DateColumn,">="&WeekBegin,DateColumn,"<="&WeekEnd,NameRange,NameToFind)

Where Date Column would be A:A,
NameRange would be B:B

You need a begin and ending date for your week. Use: =A2-MOD(A2-2,7) To get date beginning Monday. And a today() cell to reference (can be on a different sheet)

so,
A1
=Today()

B1
=A1-MOD(A1-2,7)

=B1+5 to get friday

Assuming your number of employees will be relatively stable, the graph area will stay the same.

HTH,

Jon

 

[BadFish523]

Thats what I thought. I tried this and it still doesn't count. Heres my formula, no errors just doesnt count the names. Always returns 0. =countIFS('Data'!A2:A,">="&WeekBegin,'Data'!A2:A,"<="&WeekEnd,'Data'!B2:B,"Stephen Guthrie")

I assume it has something to do with how to use the second part of your formula to get the start and end of the week. Do I put that whole thing on a different sheet and reference the sheet containing the data or just the =today cell goes on a different sheet. I'm trying to pull the data to a different sheet as you can see in the formula I included. My raw data is in the data sheet and I'm trying to pull the information for my graph to sheet4 which will then be hidden after the formulas work and graphs will be on a separate graphs sheet.

 

[JPARKHURST]

Thats what I thought. I tried this and it still doesn't count. Heres my formula, no errors just doesnt count the names. Always returns 0. =countIFS('Data'!A2:A,">="&WeekBegin,'Data'!A2:A,"<="&WeekEnd,'Data'!B2:B,"Stephen Guthrie")

I assume it has something to do with how to use the second part of your formula to get the start and end of the week. Do I put that whole thing on a different sheet and reference the sheet containing the data or just the =today cell goes on a different sheet. I'm trying to pull the data to a different sheet as you can see in the formula I included. My raw data is in the data sheet and I'm trying to pull the information for my graph to sheet4 which will then be hidden after the formulas work and graphs will be on a separate graphs sheet.

Try changing your ranges to A:A/B:B. A2:a doesn't work - what's the final row for the range? It's OK if your header is included - it will just be looked at as if it is another line. So - unless a name is also a category, then you're cool to include it.

If that doesn't work, can you post some sample data in a table and I'll take a look directly.


HTH,

Jon

 

[BadFish523]

Thank you for your help! I figured it out, it was silly. I didn't change the WeekBegin/End to the cells containing those dates. DUH! Lol. Your formula now works great! This problem is now solved thanks to your help!

 

[JPARKHURST]

More than happy to help, glad you got it working! Now, if we can just figure out how to add another Saturday into the week....

-J

 

[BadFish523]

When you figure that one out you'll be famous!!!!