Trying and failing to tally multiple column totals specific to particular values (a name) across a date range
We have a very rudimentary spreadsheet at my job which is meant to track, basically, accomplishments across time. Unfortunately, I have no one around who knows more about Excel than I do, and I don't think that I know a lot.
My goal is to be able to have a summary of values per person across a date range, and so far I have not had any success.
Here's a mockup of our spreadsheet. Imagine this across a whole year, with a tally per person each week.
Column F is using simple conditional formatting for colour, with F3 (representative of the rest of the column) being:
=(E3*60)/(C3+(D3/3))
This should represent workplace efficiency by day. Column D's values should be completable at a 3:1 ratio with Column C.
G7 (representative of the rest of the column) being:
=AVERAGEIF(B1:B25, B27, F1:F25)
This should be the average across the week.
What I'd like to be able to do would be to see a summary for each person. How many TIE Interceptors did Wedge get since January? How many TIE Fighters? How many labour hours (Column E) did he put in in that time range?
I know this is an extremely simple spreadsheet and could be heavily improved. Unfortunately, I have years of this data, and no one to help me fix/improve anything about these records, so I can't really overhaul the whole thing. I just have to work with the way that it is already set up so that I can get this information, but my formulas are not working and I am not educated enough to understand why.
I thought SUMIFS would be the best way to go, adding the weekly total per column per person, but I have not been able to get a functioning formula for it. (for Wedge, I'd want it to SUM each column if B="Wedge Antilles" and A is blank, but A=<>"" has not worked a single time for me so far. This could easily be just user error on my part, but I don't understand what I'm doing wrong. Maybe SUMIFS isn't even the ideal approach for this.
I only get to work on this dilemma at the end of long workdays of manual labour, so I've been tinkering with it (very) intermittently for a few weeks and only finally remembered today that I can consult with real people for advice.
So: How can I convert this data of stuff-per-person-per-week into a useful set of totals across a bigger time period?
[link] [comments]
Want to read more?
Check out the full article on the original site