adding sheets

[ganga]

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

 

[Jacob Skaria]

Use SUMIF() to specify a criteria. The below sums up all entries where colA
has got 'ram'.

=SUMIF(Sheet1!A:A,"ram",Sheet1!B:B)


If this post helps click Yes

 

[Jacob Skaria]

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

If your sheet names are Sheet1, Sheet2, Sheet3 then

Insert>Name>Define
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:B100"),
MyRows-2,,)))


If this post helps click Yes