| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=504775 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
total value relating to cell values in multiple columns!
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
total value relating to cell values in multiple columns!
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" wrote in message ... 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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
total value relating to cell values in multiple columns!
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 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=504775 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding Min Cell values excluding zero in alternate columns | Excel Worksheet Functions | |||
| Finding Min Cell values excluding zero in alternate columns | Excel Worksheet Functions | |||
| substract cell F from cell H and total into cell I | New Users to Excel | |||
| Extract one numerical value from single cell with multiple values? | Excel Worksheet Functions | |||
| Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Linear Mode
