SUM the rest of the row from a specific date in a pivot table?

[M-D_87]

Hallo

I am currently doing a small analysis on items locked due quality issues, and have gotten a bit of a problem.

I have a pivot table which contains the lost sales date, the item number vertically and date horizontally, the values are the amount of lost sales.

Now a have a list which contains the item number, and the creation date the quality issue.

So in order to know how much sales there has been lost since the item had quality issues i need to look up the correct item number in the pivot table, and the go to the closest date after the creation date and sum the rest of the row.

I have attached a little example i hope will clarify the issue


List containing a few 100 numbers locked on various dates

Item Number	Creation date
1111111	01-09-13

<tbody>
</tbody>

Pivot table

	01-08-13	15-09-13	01-10-13
1111111	1	2	3
2222222	1	2	3
3333333	1	3	4

<tbody>
</tbody>

So in this case it should the two numbers marked in bold italic, giving me 5.

I simple cannot get my head around this issue, so any help is appreciated. And please and if there is anything the needs clarification

Thanks in advance

 

[jamtay317]

you will have to use a formula for this, that being said I wouldn't use the data from the pivot table.

 

[M-D_87]

Why not use the pivot table? I use data from them all the time. Very easy way to set up data, the way it's needed.

I've already tried several formula combinations, but I cannot crack the code so to say.

 

[M-D_87]

The combination of match, offset and sum function I have been trying, appears to be somewhat of a dead end unfortunately.

 

[M-D_87]

Can it really be true, that there is no way to do this? It seems simply enough...

 

[jamtay317]

so on this your wanting to add 2+3?

 

[jamtay317]

Excel 2012
	A	B	C	D	E	F	G	H
2
3		8/1/2013	9/15/2013	10/1/2013		Item #	Date	sum
4	1111111	1	2	3		3333333	8/2/2013	7
5	2222222	1	2	3				#VALUE!
6	3333333	1	3	4
7
8
9
Sheet1

Cell Formulas
Range	Formula
H4	=SUMPRODUCT(INDEX(B4:D6,MATCH(F4,A4:A6,0),MATCH(G4,B3:D3)+1):INDEX(B4:D6,MATCH(F4,A4:A6,0),MATCH(9.9E+307,B4:D4)))
H5	=INDEX(B4:D6,MATCH(F4,A4:A6,0),)

this will do it, but I still dont like the pivot table data, if it gets pivoted then this formula will not work

 

[M-D_87]

Thanks a lot, ill try this tomorrow. Don't worry about the pivot table, once I have the result it is copied and inserted as values.