Summing below vlookup from an Index Array

[robfanucchi]

Hi All,

Im looking to create a formula that would sum the above values to get a total.I have used a drop down list and an Index Array along with Vlookup to get the monthly sales per product per customer.

My thoughts were that if I add an If function to the start of my vlookup formula to say that when it looks at the product code in Col A and if a product code is present then it must return the corresponding sales value or is the value is " " then it would sum all the sales data above it across all the monthly columns.

If you guys see any issues with my current formulas please let me know as well.

Any thoughts?

Thanks in advance.
Rob.

[AliGW]

With A1 in the drop-down box, what are your expected outcomes on the first row of data, please? There should be no need for IF statements if you are using the data in the table you have created. The expected outcomes will help us determine what you are aiming to do here.

[robfanucchi]

Hi AliGW

The Outcomes will be the values hi-lighted in yellow.

Basically what im looking for is when I change the brand on the drop down list,
say from a1 to c3 (the data grows from 7 line items to 11)
I want a formula to automatically sum the data above it for each column (or month) and return the data in the next empty row
(brand a1 will be row 12 and when i change to c3 the total should automatically sum all the data above it for the month and return the vale in row 16)

Hope this makes sense.

[robfanucchi]

My thoughts we that the vlookup must detect that a value is in column A and it must return the corresponding sales figure, otherwise if no value is available (which means the end of the data set) then it must sum the values above that line for each month.

[AliGW]

OK - just taking a look now. I think you will need to use the SUBTOTAL function.

[AliGW]

Mmm - can't find a way to make it work - sorry. Hopefully someone else will step in.