Summation Formula Needed

[addie]

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

 

[Bob Phillips]

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[addie]

Thanks Bob. It worked.

One more question...

If I wanted to do a vlookup that incorporated that logic, what foruml
could I use?

Addi

 

[Bob Phillips]

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)

 

[Ashish Mathur]

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

 

[Aladin Akyurek]

=SUMIF(CodeRange,"*1",RevenueRange)

=SUMIF(CodeRange,"*1",ResponseRange)

 

[Aladin Akyurek]

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