total value relating to cell values in multiple columns!

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

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

"via135" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> via135
> ------------------------------------------------------------------------
> via135's Profile:
> http://www.excelforum.com/member.php...o&userid=26725
> View this thread: http://www.excelforum.com/showthread...hreadid=504775
>

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

"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "via135" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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
>>
>>
>> --
>> via135
>> ------------------------------------------------------------------------
>> via135's Profile:
>> http://www.excelforum.com/member.php...o&userid=26725
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=504775
>>
>
>

thks Biff!

it works perfectly!

i am able to get the correct result even for a single month by giving the same month in A9(from) & A10(to)..!

regds!

-via135