Nesting Sumif function
 

Hello, if anyone can, please help.

I'm trying to nest sumif so as to check one column and if it is true, check
another column and sum the total as per usual.

ie

if (A1:A6) equals "Blue" then sumif(B1:B6,F1:F6,C1:C6)

Could anyone help me out here?? {:-{


Kind regards
Jeff

Hi

bit confused by your question and example SUMIF formula but if you mean
.........A................B.............C
1.....Blue...........North..........5
2.....Red...........North..........3
3.....Blue...........South..........2
4.....Blue..........North..........10

and you want to sum column C where the value in A is blue and the value in B
is north then use:

=SUMPRODUCT(--(A1:A4="Blue"),--(B1:B4="North"),C1:C4)

check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

for more details on using the SUMPRODUCT function

Cheers
JulieD

"Need Help eek" <Need Help wrote in message
...
Hello, if anyone can, please help.

I'm trying to nest sumif so as to check one column and if it is true,
check
another column and sum the total as per usual.

ie

if (A1:A6) equals "Blue" then sumif(B1:B6,F1:F6,C1:C6)

Could anyone help me out here?? {:-{


Kind regards
Jeff

Julie,

He might mean

=SUMPRODUCT(--(A1:A6="Blue"),--(B1:B6=F1:F6),C1:C6)

Jeff,

If so, you can also do it with

=IF(A1:A6="Blue",SUMIF(B1:B6,F1:F6,C1:C6))

which is basically what you said, and is an array formula, so commit with
Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JulieD" wrote in message
...
Hi

bit confused by your question and example SUMIF formula but if you mean
........A................B.............C
1.....Blue...........North..........5
2.....Red...........North..........3
3.....Blue...........South..........2
4.....Blue..........North..........10

and you want to sum column C where the value in A is blue and the value in
B
is north then use:

=SUMPRODUCT(--(A1:A4="Blue"),--(B1:B4="North"),C1:C4)

check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

for more details on using the SUMPRODUCT function

Cheers
JulieD

"Need Help eek" <Need Help wrote in message
...
Hello, if anyone can, please help.

I'm trying to nest sumif so as to check one column and if it is true,
check
another column and sum the total as per usual.

ie

if (A1:A6) equals "Blue" then sumif(B1:B6,F1:F6,C1:C6)

Could anyone help me out here?? {:-{


Kind regards
Jeff

Hi Bob

good point :)

Cheers
JulieD

"Bob Phillips" wrote in message
...
Julie,

He might mean

=SUMPRODUCT(--(A1:A6="Blue"),--(B1:B6=F1:F6),C1:C6)

Jeff,

If so, you can also do it with

=IF(A1:A6="Blue",SUMIF(B1:B6,F1:F6,C1:C6))

which is basically what you said, and is an array formula, so commit with
Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JulieD" wrote in message
...
Hi

bit confused by your question and example SUMIF formula but if you mean
........A................B.............C
1.....Blue...........North..........5
2.....Red...........North..........3
3.....Blue...........South..........2
4.....Blue..........North..........10

and you want to sum column C where the value in A is blue and the value
in
B
is north then use:

=SUMPRODUCT(--(A1:A4="Blue"),--(B1:B4="North"),C1:C4)

check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

for more details on using the SUMPRODUCT function

Cheers
JulieD

"Need Help eek" <Need Help wrote in
message
...
Hello, if anyone can, please help.

I'm trying to nest sumif so as to check one column and if it is true,
check
another column and sum the total as per usual.

ie

if (A1:A6) equals "Blue" then sumif(B1:B6,F1:F6,C1:C6)

Could anyone help me out here?? {:-{


Kind regards
Jeff

Bob Phillips wrote:
Julie,

He might mean

=SUMPRODUCT(--(A1:A6="Blue"),--(B1:B6=F1:F6),C1:C6)

Jeff,

If so, you can also do it with

=IF(A1:A6="Blue",SUMIF(B1:B6,F1:F6,C1:C6))

which is basically what you said, and is an array formula, so commit with
Ctrl-Shift-Enter.


I don't think these two formulas,

=SUMPRODUCT(--(A1:A6="Blue"),--(B1:B6=F1:F6),C1:C6)

and

{=IF(A1:A6="Blue",SUMIF(B1:B6,F1:F6,C1:C6))},

express equivalent computations. Try them on:

A1:C6 houses:

{"blue","a",5;"blue","a",4;"red","b",3;"blue","a", 2;"blue","a",1;"red","b",2}

F1:F6 houses:

{"a";"b";"c";"d";"e";"f"}