I am just learning how to use this function and I want to have the formula
sort through information and add up only certain ones to return a total. For
example, I have the following info:
001 210
002 210
003 210
004 210
005 210
007 210
008 210
011 210
013 210
014 210
015 210
017 210
018 210
019 210
613 210
013 215
004 219
005 219
007 219
002 230
003 230
004 230
007 230
012 230

and I want my formula to look for the following criteria in the first column
- equal to 001, 002, 003, 004 and 013 and add up those items for the 210 it
finds in the second column. thanks

If Column A is true XL numbers:

=SUMPRODUCT((A1:A24={1,2,3,4,13})*(B1:B24=210)*B1: B24)

If Column A is Text:

=SUMPRODUCT((A1:A24={"001","002","003","004","013" })*(B1:B24=210)*B1:B24)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Jo-Anne" wrote in message
...
I am just learning how to use this function and I want to have the formula
sort through information and add up only certain ones to return a total.
For
example, I have the following info:
001 210
002 210
003 210
004 210
005 210
007 210
008 210
011 210
013 210
014 210
015 210
017 210
018 210
019 210
613 210
013 215
004 219
005 219
007 219
002 230
003 230
004 230
007 230
012 230

and I want my formula to look for the following criteria in the first
column
- equal to 001, 002, 003, 004 and 013 and add up those items for the 210
it
finds in the second column. thanks

Thanks... but I think I didn't give you the complete problem sorry...each of
the rows has a dollar value as well and that is what I want to total.
001 210 386,457.57
002 210 3,806,114.61
003 210 1,792,781.41
004 210 3,025,374.91
005 210 497,083.20
007 210 293,936.77
008 210 112,555.67
011 210 725,058.59
013 210 (1,071,450.31)
014 210 31,515.30
015 210 79,453.55
017 210 82,999.17
018 210 48,249.24
019 210 45,048.69
613 210 14,687.61
013 215 46,431.13
004 219 5,855.00
005 219 180.00
007 219 120.00
002 230 41,185.91
003 230 15,132.31
004 230 27,930.47
007 230 3,654.00
012 230 4,059.11

thanks again!
Jo-Anne

"RagDyer" wrote:

If Column A is true XL numbers:

=SUMPRODUCT((A1:A24={1,2,3,4,13})*(B1:B24=210)*B1: B24)

If Column A is Text:

=SUMPRODUCT((A1:A24={"001","002","003","004","013" })*(B1:B24=210)*B1:B24)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Jo-Anne" wrote in message
...
I am just learning how to use this function and I want to have the formula
sort through information and add up only certain ones to return a total.
For
example, I have the following info:
001 210
002 210
003 210
004 210
005 210
007 210
008 210
011 210
013 210
014 210
015 210
017 210
018 210
019 210
613 210
013 215
004 219
005 219
007 219
002 230
003 230
004 230
007 230
012 230

and I want my formula to look for the following criteria in the first
column
- equal to 001, 002, 003, 004 and 013 and add up those items for the 210
it
finds in the second column. thanks

this is the formula I have so far, but when I add in another condition
besides the "001", it returns a 0 rather than a total. However at this
point, it is returning the correct total for the 001 line. How do I add in
multiple selections?

=SUMPRODUCT(--(A1:A22="001"),--(B1:B22="210"),C1:C22)

thanks
Jo-Anne

"Jo-Anne" wrote:

Thanks... but I think I didn't give you the complete problem sorry...each of
the rows has a dollar value as well and that is what I want to total.
001 210 386,457.57
002 210 3,806,114.61
003 210 1,792,781.41
004 210 3,025,374.91
005 210 497,083.20
007 210 293,936.77
008 210 112,555.67
011 210 725,058.59
013 210 (1,071,450.31)
014 210 31,515.30
015 210 79,453.55
017 210 82,999.17
018 210 48,249.24
019 210 45,048.69
613 210 14,687.61
013 215 46,431.13
004 219 5,855.00
005 219 180.00
007 219 120.00
002 230 41,185.91
003 230 15,132.31
004 230 27,930.47
007 230 3,654.00
012 230 4,059.11

thanks again!
Jo-Anne

"RagDyer" wrote:

If Column A is true XL numbers:

=SUMPRODUCT((A1:A24={1,2,3,4,13})*(B1:B24=210)*B1: B24)

If Column A is Text:

=SUMPRODUCT((A1:A24={"001","002","003","004","013" })*(B1:B24=210)*B1:B24)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Jo-Anne" wrote in message
...
I am just learning how to use this function and I want to have the formula
sort through information and add up only certain ones to return a total.
For
example, I have the following info:
001 210
002 210
003 210
004 210
005 210
007 210
008 210
011 210
013 210
014 210
015 210
017 210
018 210
019 210
613 210
013 215
004 219
005 219
007 219
002 230
003 230
004 230
007 230
012 230

and I want my formula to look for the following criteria in the first
column
- equal to 001, 002, 003, 004 and 013 and add up those items for the 210
it
finds in the second column. thanks

Did you try changing RD's suggestion of
=SUMPRODUCT((A1:A24={"001","002","003","004","013" })*(B1:B24=210)*B1:B24)
to
=SUMPRODUCT((A1:A22={"001","002","003","004","013" })*(B1:B22="210")*C1:C22)
(or arrays to 24, rather than 22, if you want to include the full range of
your example data, rather than the reduced range as in your formula)?
--
David Biddulph

"Jo-Anne" wrote in message
...
this is the formula I have so far, but when I add in another condition
besides the "001", it returns a 0 rather than a total. However at this
point, it is returning the correct total for the 001 line. How do I add
in
multiple selections?

=SUMPRODUCT(--(A1:A22="001"),--(B1:B22="210"),C1:C22)

thanks
Jo-Anne

"Jo-Anne" wrote:

Thanks... but I think I didn't give you the complete problem sorry...each
of
the rows has a dollar value as well and that is what I want to total.
001 210 386,457.57
002 210 3,806,114.61
003 210 1,792,781.41
004 210 3,025,374.91
005 210 497,083.20
007 210 293,936.77
008 210 112,555.67
011 210 725,058.59
013 210 (1,071,450.31)
014 210 31,515.30
015 210 79,453.55
017 210 82,999.17
018 210 48,249.24
019 210 45,048.69
613 210 14,687.61
013 215 46,431.13
004 219 5,855.00
005 219 180.00
007 219 120.00
002 230 41,185.91
003 230 15,132.31
004 230 27,930.47
007 230 3,654.00
012 230 4,059.11

thanks again!
Jo-Anne

"RagDyer" wrote:

If Column A is true XL numbers:

=SUMPRODUCT((A1:A24={1,2,3,4,13})*(B1:B24=210)*B1: B24)

If Column A is Text:

=SUMPRODUCT((A1:A24={"001","002","003","004","013" })*(B1:B24=210)*B1:B24)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------

"Jo-Anne" wrote in message
...
I am just learning how to use this function and I want to have the
formula
sort through information and add up only certain ones to return a
total.
For
example, I have the following info:
001 210
002 210
003 210
004 210
005 210
007 210
008 210
011 210
013 210
014 210
015 210
017 210
018 210
019 210
613 210
013 215
004 219
005 219
007 219
002 230
003 230
004 230
007 230
012 230

and I want my formula to look for the following criteria in the first
column
- equal to 001, 002, 003, 004 and 013 and add up those items for the
210
it
finds in the second column. thanks

Yippee, it works! Thanks! This is what worked!
=SUMPRODUCT((A1:A22={"001","002","003","004","013" })*(B1:B22="210")*C1:C22)
Jo-Anne

"David Biddulph" wrote:

Did you try changing RD's suggestion of
=SUMPRODUCT((A1:A24={"001","002","003","004","013" })*(B1:B24=210)*B1:B24)
to
=SUMPRODUCT((A1:A22={"001","002","003","004","013" })*(B1:B22="210")*C1:C22)
(or arrays to 24, rather than 22, if you want to include the full range of
your example data, rather than the reduced range as in your formula)?
--
David Biddulph

"Jo-Anne" wrote in message
...
this is the formula I have so far, but when I add in another condition
besides the "001", it returns a 0 rather than a total. However at this
point, it is returning the correct total for the 001 line. How do I add
in
multiple selections?

=SUMPRODUCT(--(A1:A22="001"),--(B1:B22="210"),C1:C22)

thanks
Jo-Anne

"Jo-Anne" wrote:

Thanks... but I think I didn't give you the complete problem sorry...each
of
the rows has a dollar value as well and that is what I want to total.
001 210 386,457.57
002 210 3,806,114.61
003 210 1,792,781.41
004 210 3,025,374.91
005 210 497,083.20
007 210 293,936.77
008 210 112,555.67
011 210 725,058.59
013 210 (1,071,450.31)
014 210 31,515.30
015 210 79,453.55
017 210 82,999.17
018 210 48,249.24
019 210 45,048.69
613 210 14,687.61
013 215 46,431.13
004 219 5,855.00
005 219 180.00
007 219 120.00
002 230 41,185.91
003 230 15,132.31
004 230 27,930.47
007 230 3,654.00
012 230 4,059.11

thanks again!
Jo-Anne

"RagDyer" wrote:

If Column A is true XL numbers:

=SUMPRODUCT((A1:A24={1,2,3,4,13})*(B1:B24=210)*B1: B24)

If Column A is Text:

=SUMPRODUCT((A1:A24={"001","002","003","004","013" })*(B1:B24=210)*B1:B24)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------

"Jo-Anne" wrote in message
...
I am just learning how to use this function and I want to have the
formula
sort through information and add up only certain ones to return a
total.
For
example, I have the following info:
001 210
002 210
003 210
004 210
005 210
007 210
008 210
011 210
013 210
014 210
015 210
017 210
018 210
019 210
613 210
013 215
004 219
005 219
007 219
002 230
003 230
004 230
007 230
012 230

and I want my formula to look for the following criteria in the first
column
- equal to 001, 002, 003, 004 and 013 and add up those items for the
210
it
finds in the second column. thanks