Sum Values for Date Range for Column

[dandol6]

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"

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

 

[dandol6]

I actually figured this out. I used the INDEX function.

Here is my updated formula:

=SUMIF('Tab #2'!$A:$A,">=" &'Tab #1'!B2,INDEX('Tab #1'!$1:$1,MATCH('Tab #1'!A2,'Tab #2'!$1:$1,0)))
- SUMIF('Tab #2'!$A:$A,">" &'Tab #1'!C2,INDEX('Tab #1'!$1:$1,MATCH('Tab #1'!A2,'Tab #2'!$1:$1,0)))