Thread: Horizontal and Vertical Lookup with date possible? Thanks: 0 Likes: 0

1. Horizontal and Vertical Lookup with date possible?

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?

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

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?  Reply With Quote

2. Re: Horizontal and Vertical Lookup with date possible?

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.  Reply With Quote

User Tag List

Tags for this Thread

data, formula, fruit, purchased, tab  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•