Summing Up Data Based on Codes
 

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=513864

Summing Up Data Based on Codes
 

Try this for your Revenue:

=SUMPRODUCT(B1:B100,--(RIGHT(A1:A100,1)="1"))

And for your responses:

=SUMPRODUCT(C1:C100,--(RIGHT(A1:A10,1)="1"))

HTH,
Elkar


"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
------------------------------------------------------------------------
addie's Profile: http://www.excelforum.com/member.php...o&userid=25526
View this thread: http://www.excelforum.com/showthread...hreadid=513864

Summing Up Data Based on Codes
 

Thanks Elkar. 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=513864