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:
<tbody>
</tbody>
Data tab:
<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?
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?