total value relating to cell values in multiple columns!

[via135]

hi all!

i am having data in A1 to H6.

A1 to H1 - Column Headings as under

product-price-jan-feb-mar-apr-may-jun

A2 to A6 - product names as under

pen-pencil-note-book-refill

B2 to B6 - price of each product

C2 to H6 - units sold

Now what i want is by giving values in other 3 cells
say in A8-"product name"
A9-"from month"
A10-"to month"

i should be able to get the total sales figure in A11 for a particular
"product" for a particular "period".

any help pl?

-via135

 

[Biff]

Hi!

Here's one way:

=IF(COUNTA(A8:A10)<3,"",SUM(OFFSET(A1,MATCH(A8,A2:A6,0),MATCH(A9,A1:H1,0)-1,,MATCH(A10,A1:H1,0)-MATCH(A9,A1:H1,0)+1)))

Assumes that you will always use a range of months. In other words, you
won't want to know books for Jun. If you want just a single month you can
use a simple Vlookup/Match or Index/Match/Match formula.

Biff

 

[Biff]

Ooops!

Hold on there a second!

My formula just sums the units sold.

If you want the total sales we have to add another chunk to the formula:

=IF(COUNTA(A8:A10)<3,"",SUM(OFFSET(A1,MATCH(A8,A2:A6,0),MATCH(A9,A1:H1,0)-1,,MATCH(A10,A1:H1,0)-MATCH(A9,A1:H1,0)+1))*VLOOKUP(A8,A2:B6,2,0))

Biff