software

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-quot;product namequot;
A9-quot;from monthquot;
A10-quot;to monthquot;

i should be able to get the total sales figure in A11 for a particular
quot;productquot; for a particular quot;periodquot;.

any help pl?

-via135--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=504775Hi!

Here's one way:

=IF(COUNTA(A8:A10)lt;3,quot;quot;,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

quot;via135quot; gt; wrote in
message ...
gt;
gt; hi all!
gt;
gt; i am having data in A1 to H6.
gt;
gt; A1 to H1 - Column Headings as under
gt;
gt; product-price-jan-feb-mar-apr-may-jun
gt;
gt; A2 to A6 - product names as under
gt;
gt; pen-pencil-note-book-refill
gt;
gt; B2 to B6 - price of each product
gt;
gt; C2 to H6 - units sold
gt;
gt; Now what i want is by giving values in other 3 cells
gt; say in A8-quot;product namequot;
gt; A9-quot;from monthquot;
gt; A10-quot;to monthquot;
gt;
gt; i should be able to get the total sales figure in A11 for a particular
gt; quot;productquot; for a particular quot;periodquot;.
gt;
gt; any help pl?
gt;
gt; -via135
gt;
gt;
gt; --
gt; via135
gt; ------------------------------------------------------------------------
gt; via135's Profile:
gt; www.excelforum.com/member.php...oamp;userid=26725
gt; View this thread: www.excelforum.com/showthread...hreadid=504775
gt;
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)lt;3,quot;quot;,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

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; Here's one way:
gt;
gt; =IF(COUNTA(A8:A10)lt;3,quot;quot;,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)))
gt;
gt; Assumes that you will always use a range of months. In other words, you
gt; won't want to know books for Jun. If you want just a single month you can
gt; use a simple Vlookup/Match or Index/Match/Match formula.
gt;
gt; Biff
gt;
gt; quot;via135quot; gt; wrote in
gt; message ...
gt;gt;
gt;gt; hi all!
gt;gt;
gt;gt; i am having data in A1 to H6.
gt;gt;
gt;gt; A1 to H1 - Column Headings as under
gt;gt;
gt;gt; product-price-jan-feb-mar-apr-may-jun
gt;gt;
gt;gt; A2 to A6 - product names as under
gt;gt;
gt;gt; pen-pencil-note-book-refill
gt;gt;
gt;gt; B2 to B6 - price of each product
gt;gt;
gt;gt; C2 to H6 - units sold
gt;gt;
gt;gt; Now what i want is by giving values in other 3 cells
gt;gt; say in A8-quot;product namequot;
gt;gt; A9-quot;from monthquot;
gt;gt; A10-quot;to monthquot;
gt;gt;
gt;gt; i should be able to get the total sales figure in A11 for a particular
gt;gt; quot;productquot; for a particular quot;periodquot;.
gt;gt;
gt;gt; any help pl?
gt;gt;
gt;gt; -via135
gt;gt;
gt;gt;
gt;gt; --
gt;gt; via135
gt;gt; ------------------------------------------------------------------------
gt;gt; via135's Profile:
gt;gt; www.excelforum.com/member.php...oamp;userid=26725
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=504775
gt;gt;
gt;
gt;

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) amp; A10(to)..!

regds!

-via135--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=504775