Hey all, I'm new to the forums and in need of some help. I am creating a spreadsheet which counts the number of write ups that a given team recieves throughout the course of the week. What I have are two seperate columns, one of which counts the number of writeups a manager is responsible for and another which counts the same managers write ups, but the value is the number of write ups that are older than 24 hours (They both should access the data from a 3rd column). What Im trying to do is to create a formula that accesses the data automatically and calculates the number of write ups that are older than 24 hours and places it into it's own seperate column. If anyone could help me, it would be greatly appreciated. Thanks!
Please post a zipped sample Excel 2003 or earlier sheet showing requirements.Originally Posted by VinceValdez12
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
It's not the exact attachment im working on, but all the formulas are the exact same. I cant post the original due to sensitive information placed within the cells. Thanks!
I think you're going to need to re-iterate what you need to have happen based on your sample....
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Sure, lets start at the front and work from there.
Columns A and B are meant to be people's names. Column G is meant to represent the date they got written up. If it's the current date, the date appears in black. If it's over 24 hours old, it appears in red. The status in Column S really has no bearing for this example, so we'll discard that for now.
Column N represents the team the employee belongs to. Column V addresses all of this information and lists the total amount of write ups for that team (In this case, Team Cash). From there Column W calculates a percentage of the TOTAL amount of write ups for the entire site. What I want Column X to do is Take the amount of write ups per team that are over 24 hours old and list that number in the appropriate cell (Team Cash should have 1), then calculate that percentage in Column Y (which the formula is already written).
I hope this clears things up a bit.
Try:
=SUMPRODUCT(--($N$2:$N$20=U2),--($G$2:$G$20>0),--(TODAY()-$G$2:$G$20>=1))
adjust ranges. Sumproduct doesn't accept entire ranges like N:N
Then copy down.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
You're awesome. I just adjusted the range to 20,000 so I dont have to deal with it for a LONG time. Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks