|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 17th, 2006, 10:57 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summation Formula Needed
I have a spreadsheet with several rows of data. Each row's data corresponds to a code that appears in the 1st field of the row. Example: Codes:..............Revenue:......Responses: AAAAAAA1..........$1,000.........500 BBBBBBBB3.........$2,000.........200 CCCCCCC1.........$3,000.........300 DDDDDDD2.........$4,000.........9000 What formula would enable me to sum up the revenue & responses on rows that only correspond to codes that end in '1'? Addie -- addie ------------------------------------------------------------------------ addie's Profile: http://www.excelforum.com/member.php...o&userid=25526 View this thread: http://www.excelforum.com/showthread...hreadid=513868 
|
|
#2
February 17th, 2006, 11:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summation Formula Needed
=SUMPRODUCT(--(RIGHT(A2:A20)="1"),(B2:B20+C2:C20))
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "addie" wrote in message ... I have a spreadsheet with several rows of data. Each row's data corresponds to a code that appears in the 1st field of the row. Example: Codes:..............Revenue:......Responses: AAAAAAA1..........$1,000.........500 BBBBBBBB3.........$2,000.........200 CCCCCCC1.........$3,000.........300 DDDDDDD2.........$4,000.........9000 What formula would enable me to sum up the revenue & responses on rows that only correspond to codes that end in '1'? Addie -- addie ------------------------------------------------------------------------ addie's Profile: http://www.excelforum.com/member.php...o&userid=25526 View this thread: http://www.excelforum.com/showthread...hreadid=513868
|
|
#3
February 17th, 2006, 11:26 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summation Formula Needed
Thanks Bob. It worked. One more question... If I wanted to do a vlookup that incorporated that logic, what forumla could I use? Addie -- addie ------------------------------------------------------------------------ addie's Profile: http://www.excelforum.com/member.php...o&userid=25526 View this thread: http://www.excelforum.com/showthread...hreadid=513868
|
|
#4
February 18th, 2006, 12:34 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summation Formula Needed
If the SP value is the lookup value, then just use
=VLOOKUP(SP_formula,lookup_Table,column_Offset,Fal se) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "addie" wrote in message ... Thanks Bob. It worked. One more question... If I wanted to do a vlookup that incorporated that logic, what forumla could I use? Addie -- addie ------------------------------------------------------------------------ addie's Profile: http://www.excelforum.com/member.php...o&userid=25526 View this thread: http://www.excelforum.com/showthread...hreadid=513868
|
|
#5
February 18th, 2006, 01:01 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summation Formula Needed
Hi,
Assume you have the following data in range A1:C7. In cell A9, enter 1 or 2 or 3. In B9, enter the following array formula (Ctrl+Shift+Enter) =IF(ISERROR(INDEX($A$1:B$7,SMALL(IF(1*RIGHT($A$1:$ A$7,1)=$A$9,ROW($A$1:$A$7)),ROW(1:1)),COLUMN()))," ",INDEX($A$1:B$7,SMALL(IF(1*RIGHT($A$1:$A$7,1)=$A$ 9,ROW($A$1:$A$7)),ROW(1:1)),COLUMN())) Copy this across rowes below and the column on the right. Hope this helps. If you have any further clarifications, please feel free to contact me. AAAAAAA1 1000 500 BBBBBBB3 2000 200 CCCCCC1 3000 300 DDDDDD2 4000 9000 EEEEEEE1 5000 1234 FFFFFFF2 6000 2345 GGGGGGG1 7000 7890 Regards, Ashish Mathur "addie" wrote: Thanks Bob. It worked. One more question... If I wanted to do a vlookup that incorporated that logic, what forumla could I use? Addie -- addie ------------------------------------------------------------------------ addie's Profile: http://www.excelforum.com/member.php...o&userid=25526 View this thread: http://www.excelforum.com/showthread...hreadid=513868
|
|
#6
February 18th, 2006, 08:08 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summation Formula Needed
=SUMIF(CodeRange,"*1",RevenueRange)
=SUMIF(CodeRange,"*1",ResponseRange) addie wrote: I have a spreadsheet with several rows of data. Each row's data corresponds to a code that appears in the 1st field of the row. Example: Codes:..............Revenue:......Responses: AAAAAAA1..........$1,000.........500 BBBBBBBB3.........$2,000.........200 CCCCCCC1.........$3,000.........300 DDDDDDD2.........$4,000.........9000 What formula would enable me to sum up the revenue & responses on rows that only correspond to codes that end in '1'? Addie
|
|
#7
February 18th, 2006, 08:12 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summation Formula Needed
addie wrote: [...] One more question... If I wanted to do a vlookup [...], what forumla could I use? Addie =INDEX(RevenueRange,MATCH("*1",CodeRange,0)) =INDEX(ResponseRange,MATCH("*1",CodeRange,0))
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Dynamic Range with unused formula messing up x axis on dynamic graph | [email protected] | Charts and Charting | 2 | February 2nd, 2006 08:02 PM |
| continuous sum formula needed | NN | General Discussion | 2 | November 4th, 2005 06:49 PM |
| Formula Help Needed | BDY | General Discussion | 3 | October 26th, 2005 05:35 PM |
| help on and/or formula needed | Fred | General Discussion | 5 | October 14th, 2005 03:06 PM |
| Possible If, Or formula needed | Jason Morin | Worksheet Functions | 0 | April 20th, 2004 06:09 PM |
Linear Mode
