Match Project Number & Sum of Charges Past a Certain Date

[Biggs1001]

Hi all,

I have a long list of project numbers that all have different project end dates. I need to look at each project and gather the total amount of charges that fall beyond that project's specific end date. I can do this individually with a BI system but I'm hoping there's a way to simply dump all charges for all projects into Sheet 2, then have my main sheet look up the project number and go grab all charges related to that specific project number that fall outside of that project number's end date. So the Charges Past End Date cell for Project Number 022046.00 on Sheet 1 would go out and gather all Expenditures for Project Number 022046.00 that fall after the Project End Date of 9/24/2020.

I've been toying with Sumif but I'm assuming I need to have a VLookup stuffed in there to specifically look for the project number in question, but I can't seem to get anything to work.

Any insight you all might have would be appreciated!

Shown below is what I have:

Sheet 1:

Project Number	Project End Date	Charges Past End Date
022046.00	9/24/2020	???
023011.00	10/31/2020	???
023068.00	10/31/2020	???
022022.00	12/31/2020	???

Sheet 2:

Project Number	Expenditures	Transaction Date
022046.00	200	8/1/2020
023068.00	1200	11/5/2020
023011.00	40	11/5/2020
022022.00	100	10/1/2020
022046.00	100	8/31/2020
022046.00	85	12/31/2020
023011.00	900	1/1/2021
023068.00	750	1/1/2021

 

[etaf]

does this work
=SUMIFS(Sheet2!$B$2:$B$9,Sheet2!$A$2:$A$9,Sheet1!A2,Sheet2!$C$2:$C$9,">"&Sheet1!B2)

Book9
	A	B	C
1	Project Number	Project End Date	Charges Past End Date
2	22046	9/24/20	85
3	23011	10/31/20	940
4	23068	10/1/20	1950
5	22022	12/31/20	0
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=SUMIFS(Sheet2!$B$2:$B$9,Sheet2!$A$2:$A$9,Sheet1!A2,Sheet2!$C$2:$C$9,">"&Sheet1!B2)

Book9
	A	B	C
1	Project Number	Expenditures	Transaction Date
2	22046	200	8/1/20
3	23068	1200	11/5/20
4	23011	40	11/5/20
5	22022	100	10/1/20
6	22046	100	8/31/20
7	22046	85	12/31/20
8	23011	900	1/1/21
9	23068	750	1/1/21
Sheet2

 

[Biggs1001]

does this work
=SUMIFS(Sheet2!$B$2:$B$9,Sheet2!$A$2:$A$9,Sheet1!A2,Sheet2!$C$2:$C$9,">"&Sheet1!B2)

Book9
	A	B	C
1	Project Number	Project End Date	Charges Past End Date
2	22046	9/24/20	85
3	23011	10/31/20	940
4	23068	10/1/20	1950
5	22022	12/31/20	0
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=SUMIFS(Sheet2!$B$2:$B$9,Sheet2!$A$2:$A$9,Sheet1!A2,Sheet2!$C$2:$C$9,">"&Sheet1!B2)

Book9
	A	B	C
1	Project Number	Expenditures	Transaction Date
2	22046	200	8/1/20
3	23068	1200	11/5/20
4	23011	40	11/5/20
5	22022	100	10/1/20
6	22046	100	8/31/20
7	22046	85	12/31/20
8	23011	900	1/1/21
9	23068	750	1/1/21
Sheet2

Amazing E! I can't thank you enough. I'll have to go read the documentation on Sumifs to see what I was missing on utilizing multiple criteria - I assumed I had to have a VLOOKUP to reference the specific project number and you were able to just slip it into the single function itself

Thanks again!

 

[etaf]

you are welcome
you mentioned SUMIF() which only allows 1 criteria