I believe what I am trying to do should be simple in Google Sheets formulae, but any solution based on an Excel formula should be easily transferable.
Because additional characters will be added periodically, I have a named range: "Heroes".
Heroes |
---|
Bilbo |
Gandalf |
Saruman |
Wormtongue |
Tom Bombadil |
For each hero, I have a worksheet in one overall workbook. On these worksheets, there are columns for Date, Time, Quest, and Count. Several times per day, a hero will venture out on a quest of a certain type, returning with a certain count as a prize. Each venture has its own row distinguishable by date and time. Eg-:
Date | Time | Quest | Count |
---|---|---|---|
12/4 | 3:00P | Ring | 9 |
12/5 | 8:00A | Mordor | 6 |
12/5 | 4:15P | Sting | 3 |
Meanwhile, I have a summary worksheet, on which I am manually entering (for now... bonus points to help create an =arrayformula() or equivalent to grab all unique date/time combinations from each character's worksheet) the date and time at which one or a batch of heroes are sent to quest. I am trying to figure out the formula template that will sum the counts for each quest type for each hero at the specific date and time signified by its corresponding row (starting at 12/4, 3:00P, Ring, the count should be 9, for example, which is Bilbo's prize for questing at that time; of course, other heroes are also sent out at 3:00P, resulting in prizes for the other quests, and multiple heroes may venture on the same type of quest at any given time):
Date | Time | Ring | Sting | Mordor | Moria |
---|---|---|---|---|---|
12/4 | 3:00P | 9 | 3 | 4 | 1 |
12/4 | 9:30P | 1 | 0 | 8 | 0 |
12/5 | 8:00A | 5 | 3 | 6 | 9 |
12/5 | 12:10A | 3 | 1 | 3 | 8 |
12/5 | 4:15P | 4 | 5 | 2 | 5 |
Since not every date and time in the summary sheet will exist on each hero's worksheet, I seem unable to use "SUMIFS", which functions in such a way that each sum_range and criteria_range are added on only across the same row when conditions are met. I think there is a SUMPRODUCT(), or INDEX(MATCH()) way to do this, but when including the named range to read across multiple worksheets, only the first hero's numbers were added in my tinkering with this.
I'm dancing around the solution here. Anyone care to tango ? Many thanks !
Sample Workbook for support: https://docs.google.com/spreadsheets/d/142IE9r2ip6YHsGdMr-zt_IHd6W7glqUId_UiGQnCUZs/edit?usp=sharing