i have been trying to get the total from other spreadsheet under 2 particular
code

for example:

Spreadsheet 1

Col A Col D Col F
ABC A 125
ABC A 25
ABC B 100
ABC B 20
ABD A 50
ABD A 50
ABD B 170
ABD B 30

Spreadsheet 2

Col A Col A Col B

ABC 150 120
ABD 100 200


I used formula

=SUM((TB!$B$2:$B$4900=$A2),(TB!$D$2:$D$4900="M"),( TB!$I$2:$I$4900))

but the result is 0. Please help solve this, thanks

Teresa

Teresa, Your use of column IDs in the example is a bit confusing, you show
Col A, D and F on first sheet, then on 2nd sheet column A twice?? Also, we
don't knkow which sheet is TB?

But, I think this will will help anyhow, just apply to the proper sheets/
ranges:

=SUMIF(TB!$B$2:$B$4900,$A2) + SUMIF(TB!$D$2:$D$4900,"M")
But at this point I'm confused about the final reference to Column I - as
you have no test parameter.

Now, if you are trying to add the values in that 3rd column (I) based on a
match of both of the first 2 criteria matching, then what you need is to use
the SUMPRODUCT() function as:

=SUMPRODUCT(--(TB!$B$2:$B$4900=$A2),--(TB!$D$2:$D$4900="M"),(TB!$I$2:$I$24900))

What that will do is that the first two tests will return either 1 (true) or
0 (false) as written, and it will take those two values and multiply them
times the value in column I of each row. 1 * 1 * anyValue = anyValue, but if
either is false then you get 1*0*anyValue = 0 (or 0*1*anyValue=0).

If this doesn't clear things up for you, I suggest you drop in over at the
Excel Worksheet Functions group and ask the question again. Although if you
ask for more help in this discussion, I'll keep my eye on it and will respond
here.

JLatham
MVP (Excel)

"teresa" wrote:

> i have been trying to get the total from other spreadsheet under 2 particular
> code
>
> for example:
>
> Spreadsheet 1
>
> Col A Col D Col F
> ABC A 125
> ABC A 25
> ABC B 100
> ABC B 20
> ABD A 50
> ABD A 50
> ABD B 170
> ABD B 30
>
> Spreadsheet 2
>
> Col A Col A Col B
>
> ABC 150 120
> ABD 100 200
>
>
> I used formula
>
> =SUM((TB!$B$2:$B$4900=$A2),(TB!$D$2:$D$4900="M"),( TB!$I$2:$I$4900))
>
> but the result is 0. Please help solve this, thanks
>
> Teresa