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:

A1	B	C	D
2	Fruit	8/1	9/1
3	Want	Formula?	Formula?
4	Purchased	Formula?	Formula?
5	Apple	Formula?	Formula?
6	Banana	Formula?	Formula?
7	Lemon	Formula?	Formula?

<tbody>
</tbody>

Data tab:

A1	B	C	D	E	F	G
2			8/1	8/1	9/1	9/1
3	Food	Type	Want	Purchased	Want	Purchased
4	Fruit	Apple	6	2	6
5	Fruit	Lemon	5	1	6
6	Fruit	Banana	10	3	12
7	Fruit	Apple	3	1	2

<tbody>
</tbody>

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.