Hello -
I have a project where i need to prepare a report that lists the sum of values within specific date range for a specific person. I have two tabs:
Tab #1 contains my "Report"
Tab #2 contains my document counts by month/year for each person
I understand how to use a SUMIFS formula to pull the sum of values for a date range, where i am struggling is how to create a formula so that the column that it pulls the counts from matches the name of the person listed in tab #1 - i dont want to have to hard code this for every value, i want it to be dynamic.
=SUMIF('Tab #2'!$A:$A,">=" &$B$1,'Tab #2'!$B:$B)
- SUMIF('Tab #2'!$A:$A,">" &$C$1,'Tab #2'!$B:$B)
I also know how to return the column letter within "Tab 2" that is associated with the Name in Tab #1 (see below). I just dont know how to marry these together to do what i want - or if there is a more streamlined way to do this.
=CHAR(64+MATCH(A2,'Tab #2'!$A$1:$AC$1,0))
The above would return the value of "B" indicating that this name is the header value for column B and that is the column within Tab #2 where i want to sum the values within the date range (for that name)
Any help here would be amazing!
Thanks in advance
-Dan
I have a project where i need to prepare a report that lists the sum of values within specific date range for a specific person. I have two tabs:
Tab #1 contains my "Report"
Name | Min Date | Max Date | Document Count |
Person A | 9/1/2022 | 11/30/2022 | 0 |
Person B | 9/1/2022 | 11/30/2022 | 0 |
Person C | 9/1/2022 | 11/30/2022 | 0 |
Tab #2 contains my document counts by month/year for each person
Date | Person A | Person B | Person C |
Sep/2022 | 5235 | 54 | 5116 |
Oct/2022 | 4541 | 22 | 5345 |
Nov/2022 | 1894 | 12 | 3658 |
I understand how to use a SUMIFS formula to pull the sum of values for a date range, where i am struggling is how to create a formula so that the column that it pulls the counts from matches the name of the person listed in tab #1 - i dont want to have to hard code this for every value, i want it to be dynamic.
=SUMIF('Tab #2'!$A:$A,">=" &$B$1,'Tab #2'!$B:$B)
- SUMIF('Tab #2'!$A:$A,">" &$C$1,'Tab #2'!$B:$B)
I also know how to return the column letter within "Tab 2" that is associated with the Name in Tab #1 (see below). I just dont know how to marry these together to do what i want - or if there is a more streamlined way to do this.
=CHAR(64+MATCH(A2,'Tab #2'!$A$1:$AC$1,0))
The above would return the value of "B" indicating that this name is the header value for column B and that is the column within Tab #2 where i want to sum the values within the date range (for that name)
Any help here would be amazing!
Thanks in advance
-Dan