Nesting Sumif function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
Hi Bob

good point :)

Cheers
JulieD

Bob Phillips said:
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)
Click to expand...
 
Bob said:
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.
Click to expand...

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"}
 
Back
Top