Multiple SUMIF Statements

I want to do a SUMIF that will add amounts from either
column C, D or E based on the following criteria:

For each cell in the range F2:F200, if there is a "y" in
that cell, then add the corresponding amount from column C
(i.e. if there is a "y" in cell F5, then the SUMIF will
pick up the amount in cell C5, and so on..)

For each cell in the range F2:F200, if there is no "y" in
that cell, the SUMIF (or whatever function can be used)
would look at each cell in the range G2:G200. For each
cell in the range G2:G200 with an "x" in it, look in the
corresponding cell in the range H2:H200, and if the number
in this cell is 5, then add the corresponding amount in
Column E; if the number in this cell is =5, then add the
corresponding amount in Column D.

(i.e. if there is no "y" in cell F8, then look in cell G8,
and if there is an "x" in this cell then look in cell H8,
and if the number is greater than 5, the SUMIF would pick
up the amount in cell E8; alternatively if the number in
cell H8 was =5, then the SUMIF would pick up the amount
in cell D8).

Finally (to cover all possibilities), for each cell in the
range F2:F200, if there is no "y" in that cell, the SUMIF
would look at each cell in the range G2:G200, and for each
cell with no "x" in this range, the SUMIF would pick up
the amount in the corresponding cell in column D.

I know I could do a lengthy IF statement for each cell row
from 2 to 200, but my spreadsheet is already pushing the
limits of the memory in my computer and I'm looking for
ways to simplify this.

Is there a way to do this using multiple SUMIF statements

Hi

Try
=SUMPRODUCT(($F$2:$F$200="y")*($C$2:$C$200))+SUMPR ODUCT(($F$2:$F$200"y")*(
$G$2:$G$200="y")*($H$2:$H$2005)*($E$2:$E$200))+SU MPRODUCT(($F$2:$F$200"y"
)*($G$2:$G$200="y")*($H$2:$H$200=5)*($D$2:$D$200) )+SUMPRODUCT(($F$2:$F$200
"y")*($G$2:$G$200"y")*($D$2:$D$200))


--
(When sending e-mail, use address )
Arvi Laanemets



"Gary Thomson" wrote in message
...
I want to do a SUMIF that will add amounts from either
column C, D or E based on the following criteria:

For each cell in the range F2:F200, if there is a "y" in
that cell, then add the corresponding amount from column C
(i.e. if there is a "y" in cell F5, then the SUMIF will
pick up the amount in cell C5, and so on..)

For each cell in the range F2:F200, if there is no "y" in
that cell, the SUMIF (or whatever function can be used)
would look at each cell in the range G2:G200. For each
cell in the range G2:G200 with an "x" in it, look in the
corresponding cell in the range H2:H200, and if the number
in this cell is 5, then add the corresponding amount in
Column E; if the number in this cell is =5, then add the
corresponding amount in Column D.

(i.e. if there is no "y" in cell F8, then look in cell G8,
and if there is an "x" in this cell then look in cell H8,
and if the number is greater than 5, the SUMIF would pick
up the amount in cell E8; alternatively if the number in
cell H8 was =5, then the SUMIF would pick up the amount
in cell D8).

Finally (to cover all possibilities), for each cell in the
range F2:F200, if there is no "y" in that cell, the SUMIF
would look at each cell in the range G2:G200, and for each
cell with no "x" in this range, the SUMIF would pick up
the amount in the corresponding cell in column D.

I know I could do a lengthy IF statement for each cell row
from 2 to 200, but my spreadsheet is already pushing the
limits of the memory in my computer and I'm looking for
ways to simplify this.

Is there a way to do this using multiple SUMIF statements

SUMIF can only take one criterion. For more than one, you must use
SUMPRODUCT. See specific formulas inserted within your text below:

"Gary Thomson" wrote in message
...
I want to do a SUMIF that will add amounts from either
column C, D or E based on the following criteria:

For each cell in the range F2:F200, if there is a "y" in
that cell, then add the corresponding amount from column C
(i.e. if there is a "y" in cell F5, then the SUMIF will
pick up the amount in cell C5, and so on..)

=SUMPRODUCT((F2:F200="y")*C2:C200)

For each cell in the range F2:F200, if there is no "y" in
that cell, the SUMIF (or whatever function can be used)
would look at each cell in the range G2:G200. For each
cell in the range G2:G200 with an "x" in it, look in the
corresponding cell in the range H2:H200, and if the number
in this cell is 5, then add the corresponding amount in
Column E; if the number in this cell is =5, then add the
corresponding amount in Column D.

(i.e. if there is no "y" in cell F8, then look in cell G8,
and if there is an "x" in this cell then look in cell H8,
and if the number is greater than 5, the SUMIF would pick
up the amount in cell E8; alternatively if the number in
cell H8 was =5, then the SUMIF would pick up the amount
in cell D8).

=SUMPRODUCT((F2:F200"y")*(G2:G200="x")*(H2:H200 5)*E2:E200)
+SUMPRODUCT((F2:F200"y")*(G2:G200="x")*(H2:H200 =5)*D2200)
(This is all one formula.)

Finally (to cover all possibilities), for each cell in the
range F2:F200, if there is no "y" in that cell, the SUMIF
would look at each cell in the range G2:G200, and for each
cell with no "x" in this range, the SUMIF would pick up
the amount in the corresponding cell in column D.

=SUMPRODUCT((F2:F200"y")*(G2:G200"x")*D2200)

Does this have to be entered as an array formula or not?


-----Original Message-----
Hi

Try
=SUMPRODUCT(($F$2:$F$200="y")*($C$2:$C$200))+SUMP RODUCT
(($F$2:$F$200"y")*(
$G$2:$G$200="y")*($H$2:$H$2005)*($E$2:$E$200))+S UMPRODUCT
(($F$2:$F$200"y"
)*($G$2:$G$200="y")*($H$2:$H$200=5)*($D$2:$D$200 ))
+SUMPRODUCT(($F$2:$F$200
"y")*($G$2:$G$200"y")*($D$2:$D$200))


--
(When sending e-mail, use address )
Arvi Laanemets



"Gary Thomson"
wrote in message
...
I want to do a SUMIF that will add amounts from either
column C, D or E based on the following criteria:

For each cell in the range F2:F200, if there is a "y" in
that cell, then add the corresponding amount from
column C
(i.e. if there is a "y" in cell F5, then the SUMIF will
pick up the amount in cell C5, and so on..)

For each cell in the range F2:F200, if there is no "y"
in
that cell, the SUMIF (or whatever function can be used)
would look at each cell in the range G2:G200. For each
cell in the range G2:G200 with an "x" in it, look in the
corresponding cell in the range H2:H200, and if the
number
in this cell is 5, then add the corresponding amount in
Column E; if the number in this cell is =5, then add
the
corresponding amount in Column D.

(i.e. if there is no "y" in cell F8, then look in cell
G8,
and if there is an "x" in this cell then look in cell
H8,
and if the number is greater than 5, the SUMIF would
pick
up the amount in cell E8; alternatively if the number in
cell H8 was =5, then the SUMIF would pick up the amount
in cell D8).

Finally (to cover all possibilities), for each cell in
the
range F2:F200, if there is no "y" in that cell, the
SUMIF
would look at each cell in the range G2:G200, and for
each
cell with no "x" in this range, the SUMIF would pick up
the amount in the corresponding cell in column D.

I know I could do a lengthy IF statement for each cell
row
from 2 to 200, but my spreadsheet is already pushing the
limits of the memory in my computer and I'm looking for
ways to simplify this.

Is there a way to do this using multiple SUMIF
statements


.

No.

"Gary Thomson" wrote in
message ...
Does this have to be entered as an array formula or not?


-----Original Message-----
Hi

Try
=SUMPRODUCT(($F$2:$F$200="y")*($C$2:$C$200))+SUMP RODUCT
(($F$2:$F$200"y")*(
$G$2:$G$200="y")*($H$2:$H$2005)*($E$2:$E$200))+S UMPRODUCT
(($F$2:$F$200"y"
)*($G$2:$G$200="y")*($H$2:$H$200=5)*($D$2:$D$200 ))
+SUMPRODUCT(($F$2:$F$200
"y")*($G$2:$G$200"y")*($D$2:$D$200))


--
(When sending e-mail, use address )
Arvi Laanemets



"Gary Thomson"
wrote in message
...
I want to do a SUMIF that will add amounts from either
column C, D or E based on the following criteria:

For each cell in the range F2:F200, if there is a "y" in
that cell, then add the corresponding amount from
column C
(i.e. if there is a "y" in cell F5, then the SUMIF will
pick up the amount in cell C5, and so on..)

For each cell in the range F2:F200, if there is no "y"
in
that cell, the SUMIF (or whatever function can be used)
would look at each cell in the range G2:G200. For each
cell in the range G2:G200 with an "x" in it, look in the
corresponding cell in the range H2:H200, and if the
number
in this cell is 5, then add the corresponding amount in
Column E; if the number in this cell is =5, then add
the
corresponding amount in Column D.

(i.e. if there is no "y" in cell F8, then look in cell
G8,
and if there is an "x" in this cell then look in cell
H8,
and if the number is greater than 5, the SUMIF would
pick
up the amount in cell E8; alternatively if the number in
cell H8 was =5, then the SUMIF would pick up the amount
in cell D8).

Finally (to cover all possibilities), for each cell in
the
range F2:F200, if there is no "y" in that cell, the
SUMIF
would look at each cell in the range G2:G200, and for
each
cell with no "x" in this range, the SUMIF would pick up
the amount in the corresponding cell in column D.

I know I could do a lengthy IF statement for each cell
row
from 2 to 200, but my spreadsheet is already pushing the
limits of the memory in my computer and I'm looking for
ways to simplify this.

Is there a way to do this using multiple SUMIF
statements


.