Horizontal and Vertical Lookup with date possible?

[PaigeHer]

I am wrecking my brain over what formula to use to summarize data (into a chart) using the data tab.

Below on the Summary tab I would like to know how many Fruit, total AND individual, do I want and how many I've purchased for each date. As time passes, I will change the date in Row 2 and I would like if the data still pulled the data into the summary table.

Summary tab:
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A1[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Fruit[/TD]
[TD]8/1[/TD]
[TD]9/1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Want[/TD]
[TD]Formula?[/TD]
[TD]Formula?[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Purchased[/TD]
[TD]Formula?[/TD]
[TD]Formula?[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]Apple[/TD]
[TD]Formula?[/TD]
[TD]Formula?[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]Banana[/TD]
[TD]Formula?[/TD]
[TD]Formula?[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]Lemon[/TD]
[TD]Formula?[/TD]
[TD]Formula?[/TD]
[/TR]
</tbody>[/TABLE]











Data tab:
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A1[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]8/1[/TD]
[TD="align: center"]8/1[/TD]
[TD="align: center"]9/1[/TD]
[TD="align: center"]9/1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Food[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Want[/TD]
[TD="align: center"]Purchased[/TD]
[TD="align: center"]Want[/TD]
[TD="align: center"]Purchased[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Fruit[/TD]
[TD="align: center"]Apple[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Fruit[/TD]
[TD="align: center"]Lemon[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Fruit[/TD]
[TD="align: center"]Banana[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Fruit[/TD]
[TD="align: center"]Apple[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I've tried a Vlookup, =VLOOKUP($B4,Data!$D:$DD,2,FALSE), to return data but I would need to change the column number for each formula.

I've also tried Sum Product, =SUMPRODUCT((Data!B3:B500=B2)*(Data!E2:DL2=B3)*(Data!E1:DL1=C2),Data!E3:DL500), but that isn't working either.

Any other suggestions on how this can work?

 

[onlyadrafter]

Hello,

In cell B2 (next to want) enter

=SUMPRODUCT(--(Data!$C$1:$F$1=B$1)*(--(Data!$C$2:$F$2=$A2)*(Data!$C$3:$F$6)))

and copy down a row and across as far as required.

In cell B4 (next to Apple) enter

=SUMPRODUCT(--(Data!$C$1:$F$1=B$1)*(--(Data!$B$3:$B$6=$A4)*(Data!$C$3:$F$6)))

and copy down a row and across as far as required.

You will need to tweak to ranges to suit your exact requirements. In my example DATA tab, C1:F1 are the dates, C2:F2 = WANT/PURCHASED, C3:F6 are the values. B3:B6 = Fruit Types.