Excel Help

2,076 Views | 9 Replies | Last: 3 yr ago by Caliber
harge57
User Profile Ignore User Stop Ignoring
How long do you want to ignore this user?
24 hours One week Permanently Cancel
10:30a, 7/9/20
AG
For each column I would like to count the total number of rows that had a 1 to the left and are now blank. (i.e. for R1 I would like to count week 3.)

This sheet represents resources joining and leaving a project. I would like to count the number of resources leaving the project each week.

I can't just sum the rows and take the difference as that does not account for new resources coming on and will miss resources leaving. Thinking some sort of CountIF, but can't quite get it.



And for the favor.

Reply Quote
10
4 edits
John Francis Donaghy
User Profile Ignore User Stop Ignoring
How long do you want to ignore this user?
24 hours One week Permanently Cancel
10:32a, 7/9/20
Here you go: https://texags.com/forums/12/topics/3123524
Reply Quote
harge57
User Profile Ignore User Stop Ignoring
How long do you want to ignore this user?
24 hours One week Permanently Cancel
11:05a, 7/9/20
AG
Think I figured it out with CountIfs

Thanks
Reply Quote
1
Lightning Dexter
User Profile Ignore User Stop Ignoring
How long do you want to ignore this user?
24 hours One week Permanently Cancel
11:10a, 7/9/20
If the count ifs don't work, look into using an array function. {=sum(if(B2:B9-C2:C9=1,1,""))} or some variation of this.
Reply Quote
jh0400
User Profile
Private Message
Ignore User Stop Ignoring
How long do you want to ignore this user?
24 hours One week Permanently Cancel
11:18a, 7/9/20
AG
If that's the extent of your table I'd just create a second one using if(and) to get to the answer. Much simpler than writing a complicated formula.
Reply Quote
jm94
User Profile Ignore User Stop Ignoring
How long do you want to ignore this user?
24 hours One week Permanently Cancel
11:19a, 7/9/20
AG
Jebus that gif is mesmerizing.
Reply Quote
4
AliasMan02
User Profile Ignore User Stop Ignoring
How long do you want to ignore this user?
24 hours One week Permanently Cancel
12:30p, 7/9/20
AG
This is weird, but this is the thread that has made me decide to do no more work today. Combination of the gif and being enticed to go work on some Excel challenges of my own did it.
Reply Quote
1
King moto moto
User Profile Ignore User Stop Ignoring
How long do you want to ignore this user?
24 hours One week Permanently Cancel
In reply to AliasMan02 • 6:33p, 7/9/20
AG
AliasMan02 said:
This is weird, but this is the thread that has made me decide to do no more work today. Combination of the gif and being enticed to go work on some Excel challenges of my own did it.
is that your code for chokin' the chicken???
Ol Army 01 Post Boobs
Reply Quote
4
rambo_99
User Profile Ignore User Stop Ignoring
How long do you want to ignore this user?
24 hours One week Permanently Cancel
2:46a, 7/11/20
My advice for quick excel turnarounds is to keep it simple - copy and paste value your entire table right below the existing table or in a new tab. Then in the newly pasted table just run a simple column 2 minus column 1 (repeating through the Time period) - if the answer is 0 then no change for that weekly period. If the answer is negative you removed headcount for that week. If the answer is positive you added someone for that week.
Reply Quote
Drawkcab
User Profile Ignore User Stop Ignoring
How long do you want to ignore this user?
24 hours One week Permanently Cancel
In reply to rambo_99 • 5:15a, 7/11/20
rambo_99 said:
My advice for quick excel turnarounds is to keep it simple - copy and paste value your entire table right below the existing table or in a new tab. Then in the newly pasted table just run a simple column 2 minus column 1 (repeating through the Time period) - if the answer is 0 then no change for that weekly period. If the answer is negative you removed headcount for that week. If the answer is positive you added someone for that week.

This is my exact method as a financial auditor. When a client sends a new spreadsheet and says "I made changes to cell X" I compare the tables to make sure there are no other changes they didn't mention.
Reply Quote
Caliber
User Profile
Private Message
Ignore User Stop Ignoring
How long do you want to ignore this user?
24 hours One week Permanently Cancel
In reply to rambo_99 • 8:48p, 7/12/20
AG
rambo_99 said:
My advice for quick excel turnarounds is to keep it simple - copy and paste value your entire table right below the existing table or in a new tab. Then in the newly pasted table just run a simple column 2 minus column 1 (repeating through the Time period) - if the answer is 0 then no change for that weekly period. If the answer is negative you removed headcount for that week. If the answer is positive you added someone for that week.

How does that capture a resource leaving if a new one started? He is dealing with turnover type stuff not total headcount.

Countifs was definitely an easy solution.

=Countifs(C1:C10,"",B1:B10,1)
*Ranges to be set as needed

Fill across the weeks needed.

That would count any current blank cell with a 1 to the left of it.
Reply Quote
Refresh
Page 1 of 1
 
×
subscribe Verify your student status
See Subscription Benefits
Trial only available to users who have never subscribed or participated in a previous trial.