software

I need to sum different criterias. I have three columns. A, B, and C.

Sample

1234 1235 20
1235 1231 15
1236 1236 10

I need the formula to match the data in column B to column A. If it
match, the formula must total the amount in column C.

I hope someone can help me with this.

Thanks in advance!--
japorms
------------------------------------------------------------------------
japorms's Profile: www.excelforum.com/member.php...foamp;userid=6544
View this thread: www.excelforum.com/showthread...hreadid=537219If I am reading the request correctly you would only have one result where
1236 = 1236. In that case, you would need an IF formula. =IF(B4=A4,C4,0).
This states that if the amount in column B equals column A take the column C
value. If it does not use 0 value. You can change 0 value to anything you
would like, if you would like words replace 0 with quot;wordsquot;

quot;japormsquot; wrote:

gt;
gt; I need to sum different criterias. I have three columns. A, B, and C.
gt;
gt; Sample
gt;
gt; 1234 1235 20
gt; 1235 1231 15
gt; 1236 1236 10
gt;
gt; I need the formula to match the data in column B to column A. If it
gt; match, the formula must total the amount in column C.
gt;
gt; I hope someone can help me with this.
gt;
gt; Thanks in advance!
gt;
gt;
gt; --
gt; japorms
gt; ------------------------------------------------------------------------
gt; japorms's Profile: www.excelforum.com/member.php...foamp;userid=6544
gt; View this thread: www.excelforum.com/showthread...hreadid=537219
gt;
gt;

If you want a single cell to contain the sum from column C of all
corresponding matches between A and B in rows 1 to 100 (for example),
you can use this formula:

=SUM(IF((A1:A100=B1:B100),C1:C100,0))

It is an array formula, which means that once you have typed it in (or
subsequently edit it) you have to use CTRL-SHIFT-ENTER instead of just
ENTER. If you do this correctly then Excel will wrap curly braces { }
around the formula - you must not type these yourself.

Hope this helps.

Pete
Nope, its not exactly like that

I'll rephrase my question. I think I'm a bit off in my first post.

There will be 2 sheet now to make it much clearer. Sheet1 and Sheet2

In sheet1, the data are the Serial No. and Amount like so:

1235 10
1236 20
1230 30

In sheet2, the data are serial no only, like so:

1236
1232
1230

I need to sum all the amount that will match in sheet2

like for my example sheet will match with 1230 and 1236, so the results
should be 50.

I've been trying a lot of formulas, and it's confusing me. I hope you
can help me with this one. This one is a challenge.

Thanks!

T Duquette Wrote:
gt; If I am reading the request correctly you would only have one result
gt; where
gt; 1236 = 1236. In that case, you would need an IF formula.
gt; =IF(B4=A4,C4,0).
gt; This states that if the amount in column B equals column A take the
gt; column C
gt; value. If it does not use 0 value. You can change 0 value to anything
gt; you
gt; would like, if you would like words replace 0 with quot;wordsquot;
gt;
gt; quot;japormsquot; wrote:
gt;
gt; gt;
gt; gt; I need to sum different criterias. I have three columns. A, B, and
gt; C.
gt; gt;
gt; gt; Sample
gt; gt;
gt; gt; 1234 1235 20
gt; gt; 1235 1231 15
gt; gt; 1236 1236 10
gt; gt;
gt; gt; I need the formula to match the data in column B to column A. If it
gt; gt; match, the formula must total the amount in column C.
gt; gt;
gt; gt; I hope someone can help me with this.
gt; gt;
gt; gt; Thanks in advance!
gt; gt;
gt; gt;
gt; gt; --
gt; gt; japorms
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; japorms's Profile:
gt; www.excelforum.com/member.php...foamp;userid=6544
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=537219
gt; gt;
gt; gt;--
japorms
------------------------------------------------------------------------
japorms's Profile: www.excelforum.com/member.php...foamp;userid=6544
View this thread: www.excelforum.com/showthread...hreadid=537219
It's still not working, the results should be 50--
japorms
------------------------------------------------------------------------
japorms's Profile: www.excelforum.com/member.php...foamp;userid=6544
View this thread: www.excelforum.com/showthread...hreadid=537219=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!A1:A10,Sheet2!A1:A3,0))),Sh eet1!B1:B10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;japormsquot; gt; wrote in
message ...
gt;
gt; Nope, its not exactly like that
gt;
gt; I'll rephrase my question. I think I'm a bit off in my first post.
gt;
gt; There will be 2 sheet now to make it much clearer. Sheet1 and Sheet2
gt;
gt; In sheet1, the data are the Serial No. and Amount like so:
gt;
gt; 1235 10
gt; 1236 20
gt; 1230 30
gt;
gt; In sheet2, the data are serial no only, like so:
gt;
gt; 1236
gt; 1232
gt; 1230
gt;
gt; I need to sum all the amount that will match in sheet2
gt;
gt; like for my example sheet will match with 1230 and 1236, so the results
gt; should be 50.
gt;
gt; I've been trying a lot of formulas, and it's confusing me. I hope you
gt; can help me with this one. This one is a challenge.
gt;
gt; Thanks!
gt;
gt; T Duquette Wrote:
gt; gt; If I am reading the request correctly you would only have one result
gt; gt; where
gt; gt; 1236 = 1236. In that case, you would need an IF formula.
gt; gt; =IF(B4=A4,C4,0).
gt; gt; This states that if the amount in column B equals column A take the
gt; gt; column C
gt; gt; value. If it does not use 0 value. You can change 0 value to anything
gt; gt; you
gt; gt; would like, if you would like words replace 0 with quot;wordsquot;
gt; gt;
gt; gt; quot;japormsquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; I need to sum different criterias. I have three columns. A, B, and
gt; gt; C.
gt; gt; gt;
gt; gt; gt; Sample
gt; gt; gt;
gt; gt; gt; 1234 1235 20
gt; gt; gt; 1235 1231 15
gt; gt; gt; 1236 1236 10
gt; gt; gt;
gt; gt; gt; I need the formula to match the data in column B to column A. If it
gt; gt; gt; match, the formula must total the amount in column C.
gt; gt; gt;
gt; gt; gt; I hope someone can help me with this.
gt; gt; gt;
gt; gt; gt; Thanks in advance!
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; japorms
gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; japorms's Profile:
gt; gt; www.excelforum.com/member.php...foamp;userid=6544
gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=537219
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt; --
gt; japorms
gt; ------------------------------------------------------------------------
gt; japorms's Profile:
www.excelforum.com/member.php...foamp;userid=6544
gt; View this thread: www.excelforum.com/showthread...hreadid=537219
gt;

Thanks Bob! It's working great now.Bob Phillips Wrote:
gt; =SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!A1:A10,Sheet2!A1:A3,0))),Sh eet1!B1:B10)
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;japormsquot; gt; wrote
gt; in
gt; message ...
gt; gt;
gt; gt; Nope, its not exactly like that
gt; gt;
gt; gt; I'll rephrase my question. I think I'm a bit off in my first post.
gt; gt;
gt; gt; There will be 2 sheet now to make it much clearer. Sheet1 and
gt; Sheet2
gt; gt;
gt; gt; In sheet1, the data are the Serial No. and Amount like so:
gt; gt;
gt; gt; 1235 10
gt; gt; 1236 20
gt; gt; 1230 30
gt; gt;
gt; gt; In sheet2, the data are serial no only, like so:
gt; gt;
gt; gt; 1236
gt; gt; 1232
gt; gt; 1230
gt; gt;
gt; gt; I need to sum all the amount that will match in sheet2
gt; gt;
gt; gt; like for my example sheet will match with 1230 and 1236, so the
gt; results
gt; gt; should be 50.
gt; gt;
gt; gt; I've been trying a lot of formulas, and it's confusing me. I hope
gt; you
gt; gt; can help me with this one. This one is a challenge.
gt; gt;
gt; gt; Thanks!
gt; gt;
gt; gt; T Duquette Wrote:
gt; gt; gt; If I am reading the request correctly you would only have one
gt; result
gt; gt; gt; where
gt; gt; gt; 1236 = 1236. In that case, you would need an IF formula.
gt; gt; gt; =IF(B4=A4,C4,0).
gt; gt; gt; This states that if the amount in column B equals column A take
gt; the
gt; gt; gt; column C
gt; gt; gt; value. If it does not use 0 value. You can change 0 value to
gt; anything
gt; gt; gt; you
gt; gt; gt; would like, if you would like words replace 0 with quot;wordsquot;
gt; gt; gt;
gt; gt; gt; quot;japormsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; I need to sum different criterias. I have three columns. A, B,
gt; and
gt; gt; gt; C.
gt; gt; gt; gt;
gt; gt; gt; gt; Sample
gt; gt; gt; gt;
gt; gt; gt; gt; 1234 1235 20
gt; gt; gt; gt; 1235 1231 15
gt; gt; gt; gt; 1236 1236 10
gt; gt; gt; gt;
gt; gt; gt; gt; I need the formula to match the data in column B to column A. If
gt; it
gt; gt; gt; gt; match, the formula must total the amount in column C.
gt; gt; gt; gt;
gt; gt; gt; gt; I hope someone can help me with this.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks in advance!
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; japorms
gt; gt; gt; gt;
gt; gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; gt; japorms's Profile:
gt; gt; gt; www.excelforum.com/member.php...foamp;userid=6544
gt; gt; gt; gt; View this thread:
gt; gt; gt; www.excelforum.com/showthread...hreadid=537219
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; japorms
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; japorms's Profile:
gt; www.excelforum.com/member.php...foamp;userid=6544
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=537219
gt; gt;--
japorms
------------------------------------------------------------------------
japorms's Profile: www.excelforum.com/member.php...foamp;userid=6544
View this thread: www.excelforum.com/showthread...hreadid=537219