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

=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

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

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

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

=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

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))