...not even sure how to describe it...

[pierre robinson]

Evening all.

Im trying to sum up costs for maintenance work on a property between date ranges

I have 2 sets of data:

The first set of data, Table A, has a Unique ID and a start and finish date for the work set out in rows.
The second set, Table B, has the same Unique ID, multiple invoices against that UID and the respective invoice dates when they were submitted in columns.

What I THINK im trying to do is come up with a formula that says;

For this UID in Table A, between these dates in Table A, match the UID in Table B, between the same date range as Table A & the sum the invoices .

Examples below:

Table A

[TABLE="width: 500"]
<tbody>[TR]
[TD] UID[/TD]
[TD]Start Date[/TD]
[TD]Stop Date[/TD]
[/TR]
[TR]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="align: center"]PFTS001116[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]01/01/18[/TD]
[TD]01/03/18[/TD]
[/TR]
</tbody>[/TABLE]


Table B

[TABLE="width: 500"]
<tbody>[TR]
[TD]UID[/TD]
[TD]Invoice date[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]PFTS0011

16​

[/TD]
[TD]01/02/18[/TD]
[TD]276.50[/TD]
[/TR]
[TR]
[TD]PFTS0011

16​

[/TD]
[TD]22/02/18[/TD]
[TD]1198.00[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]PFTS0011

16​

[/TD]
[TD]23/02/18[/TD]
[TD]1275.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




Any genius ideas?

TIA

 

[jtakw]

Hi,

You can use SUMIFS:

Book1
	A	B	C	D	E
1	Table A
2
3	UID	Start Date	Stop Date		Cost
4	PFTS001116	1/1/2018	3/1/2018		2749.5
5
6
7
8	Table B
9
10	UID	Invoice date	Cost
11	PFTS001116	2/1/2018	276.5
12	PFTS001117	2/15/2018	550
13	PFTS001116	2/22/2018	1198
14	PFTS001117	2/27/2018	900
15	PFTS001116	2/23/2018	1275
16	PFTS001116	3/2/2018	125
17	PFTS001116	4/1/2018	220
Master

Cell Formulas
Range	Formula
E4	=SUMIFS(C11:C17,A11:A17,A4,B11:B17,">="&B4,B11:B17,"<="&C4)