I'm working on a budget in an Excel worksheet. I have various items with columns for Quantity (Column G), Price (Column H), Purchased (Y/N) (Column I). I've been able to create a total through the SUMPRODUCT formula that multiplies Column G with the corresponding row cell in Column H. Here is the formula I am using:
=SUMPRODUCT(G2:G67,H2:H67)
The first issue I'm having is that I'd like to use a filter with this worksheet. So, if I filter on a particular Item Type (Column A), the SUMPRODUCT total will change to show only the cells that show up in the filter. Any suggestions?
The second issue I'm having is that I'd like to use the filter to calculate the total similar to above but, adding a criteria on those items that I haven't purchased yet, Column I. In Column "I", I have either a "Y" or a "N" in each cell to represent whether I've purchased it or not. I'd also like to filter the list and have this change accordingly similar to the first issue I'm having.
Any advice would be greatly appreciated. Thanks...
Bookmarks