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

=SUMPRODUCT(--(RIGHT(A2:A20)="1"),(B2:B20+C2:C20))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"addie" <addie.23eblz_1140217202.189@excelforum-nospam.com> wrote in message
news:addie.23eblz_1140217202.189@excelforum-nospam.com...
>
> 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

If the SP value is the lookup value, then just use

=VLOOKUP(SP_formula,lookup_Table,column_Offset,False)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"addie" <addie.23eczz_1140219003.2447@excelforum-nospam.com> wrote in
message news:addie.23eczz_1140219003.2447@excelforum-nospam.com...
>
> 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))