- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Any excel gurus?
Posted on 7/9/20 at 7:58 pm
Posted on 7/9/20 at 7:58 pm
Any excel gurus? Trying to figure out the best way to tally how many projects an employee has in progress. For example, I only want it to tally for a specific employee if the project start date has passed but the project end date has not. The goal is to have a quick dashboard of how many active projects an employee has so we can assign new projects based on workload. I played around with COUNTIFS but can’t seem to figure it out.
This post was edited on 7/9/20 at 8:02 pm
4
Posted on 7/9/20 at 8:42 pm to Furbs311
Use DATEDIF to compare the current date to starting & ending dates
Increment employee's project count when the current date falls in between the starting & ending dates
Increment employee's project count when the current date falls in between the starting & ending dates
Posted on 7/9/20 at 9:06 pm to Furbs311
lot of ways to skin this cat, but if you're set on countifs, this should work. this will be placed next to the unique list of employees (& # of active projects), so another table. in the example below, the unique list of names is on column G
=COUNTIFS($A$2:$A$12,G1,$B$2:$B$12,"<="&$J$8,$D$2:$D$12,">"&$J$8)
A-A is employees range
G1 is first empolyee on unique list of names
B-B is start date range
D-D is end date range
J8 is a random cell in which you enter =TODAY
=COUNTIFS($A$2:$A$12,G1,$B$2:$B$12,"<="&$J$8,$D$2:$D$12,">"&$J$8)
A-A is employees range
G1 is first empolyee on unique list of names
B-B is start date range
D-D is end date range
J8 is a random cell in which you enter =TODAY
Posted on 7/9/20 at 9:18 pm to castorinho
Not set on COUNTIF if there’s a better way. That’s just the first thing my simpleton brain gravitated towards!
Posted on 7/9/20 at 10:03 pm to Furbs311
Use CountIfs
= CountIfs(NameRange, “=Joe”, StartRange, “<”&Today(), EndRange, “>”&Today())
The “Today()” part is a function so it must be combined with the string using “&” inside the criteria.
ETA: You can also edit the “=Joe” part to fill out a table. Replace with “=“&TableNameCell where TableNameCell is the cell of your table with the desired name that you want to count.
= CountIfs(NameRange, “=Joe”, StartRange, “<”&Today(), EndRange, “>”&Today())
The “Today()” part is a function so it must be combined with the string using “&” inside the criteria.
ETA: You can also edit the “=Joe” part to fill out a table. Replace with “=“&TableNameCell where TableNameCell is the cell of your table with the desired name that you want to count.
This post was edited on 7/9/20 at 10:08 pm
Posted on 7/10/20 at 7:59 pm to castorinho
This worked great, thanks.
New question - is there a way to do a “simple” tally between workbooks? For example, what I’m trying to do is:
Column A=contract quarter
Column B=Employee name
=countifs(‘book1’!$A:$A+’book2’!$A:$A,”contract quarter”,’book1’!$B:$B+’book2’!$B:B,”employee name”)
I built this using the function arguments box, but get an error
New question - is there a way to do a “simple” tally between workbooks? For example, what I’m trying to do is:
Column A=contract quarter
Column B=Employee name
=countifs(‘book1’!$A:$A+’book2’!$A:$A,”contract quarter”,’book1’!$B:$B+’book2’!$B:B,”employee name”)
I built this using the function arguments box, but get an error
This post was edited on 7/10/20 at 8:01 pm
Posted on 7/10/20 at 9:34 pm to Furbs311
quote:
=countifs(‘book1’!$A:$A+’book2’!$A:$A,”contract quarter”,’book1’!$B:$B+’book2’!$B:B,”employee name”)
Not sure exactly what you’re trying to do, but you can’t combine ranges like that. Separate each page into its own CountIfs and add them.
=CountIfs(Book1Range, “Contract Quarter”, Book1Range, “Employee Name”) + Countifs(Book2Range, “Contract Quarter”, Book2Range, “Employee Name”)
Is that what you’re trying to do?
Posted on 7/10/20 at 9:46 pm to LSUtigerME
Yeah exactly. When I tried that earlier, it would only work if there was at least one value in each book.
For example, if “Joe” didn’t have a 3Q project in book 1, but had one in book2, the formula would return “0”. But if I assign a 3Q project to Joe in book1, the formula would then count both and return “2”. If that makes sense..
For example, if “Joe” didn’t have a 3Q project in book 1, but had one in book2, the formula would return “0”. But if I assign a 3Q project to Joe in book1, the formula would then count both and return “2”. If that makes sense..
Posted on 7/10/20 at 10:08 pm to Furbs311
quote:
For example, if “Joe” didn’t have a 3Q project in book 1, but had one in book2, the formula would return “0”. But if I assign a 3Q project to Joe in book1, the formula would then count both and return “2”. If that makes sense..
That doesn’t make sense unless you combined them under one “CountIfs” like the formula you posted. With separate CountIfs it will evaluate each page independently. It’s like having a CountIfs in a cell on each page, and then just adding the numbers.
Use the Formula tab and the Evaluate/“Step Into” function. It should result in 1+0 in that scenario.
Posted on 7/13/20 at 1:11 pm to LSUtigerME
Not sure how to google this - can two cells be the “same”?
For example, if I put “Joe” in A1, it changes B1 to “Joe”. But if I put “Tim” in B1, it changes A1 to “Tim”.
For example, if I put “Joe” in A1, it changes B1 to “Joe”. But if I put “Tim” in B1, it changes A1 to “Tim”.
Posted on 7/13/20 at 1:35 pm to Furbs311
quote:Mirror cells so that a change in either cell updates the other
Not sure how to google this - can two cells be the “same”?
For example, if I put “Joe” in A1, it changes B1 to “Joe”. But if I put “Tim” in B1, it changes A1 to “Tim”.
Posted on 7/13/20 at 1:38 pm to Korkstand
Mirror! That’s the word I was looking for 
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News