To have a single formula which works across sheets try the below.

If your sheet names are Sheet1, Sheet2, Sheet3 then

InsertNameDefine
Name: MyRows
Refers to =ROW(INDIRECT("1:100"))

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'Sheet"
&{"1","2","3"}&"'!A2:A100"),MyRows-2,,))="ram"),
N(OFFSET(INDIRECT("'Sheet"&{"1","2","3"}&"'!B2:B10 0"),
MyRows-2,,)))


If this post helps click Yes
---------------
Jacob Skaria


"ganga" wrote:

i want to choose only certain names from sheets and add those numbers in last
sheet..

example
sheet 1
ram 100
kala 100
ann 100
ann 100
ram 500

sheet 2
kala 200
ann 150
kala 500
ram 250
ram 250

sheet 3
ann 500
ann 500
lina 250
ram 600
kala 300

and so on

in Sheet 6
i want to choose only ram's #s from others sheets and add, and kala's #s and
add an so on...but those are not always in the same row.. row # changes sheet
by sheet and sometime same name is there more than once..

in sheet 6
ram (a1) sum(100+500+250+250+600)(b1)

I don't want to choose sheet by sheet every time..