strange SUM behaviour

  • Thread starter Thread starter kleimeier
  • Start date Start date
K

kleimeier

Hi,

I have made a sheet containing my planning, instead of using MS
project.
In column A I place department code
In column B I place peron initials
in column c..........etc I place planned hours/day/item
Upto now I have like 100 rows and 300 columns.
in row 100 till 105 I like to sum/department the total planned hours
with formula sumif(A1:B100, "infra", c1:c100). I did some spot checks
and founf that some, not all, calculations where wrong. I checked
against a simple SUM from column c which gave the correct calculation.

Is there a limmit to sumif or does anybody else has a clue or other
sollution?

thanks in advance,

Ton
 
No but sumif will only return hits from one column (Yoiu have 2, A & B), if
you need A1:B100 you can use

=SUMPRODUCT(--(A1:B100="infra")*(C1:C100))
 
Ton
Is it possible that some of your cells containing "infra" might have spaces in them also, or other text? Try using

=SUMIF(A1:A100, "*infra*", C1:C100

I also changed the range to column A only. As Peo stated, SUMIF will only look at one column

Regards
Mark Graesse
(e-mail address removed)

----- kleimeier > wrote: ----

Hi

I have made a sheet containing my planning, instead of using M
project
In column A I place department cod
In column B I place peron initial
in column c..........etc I place planned hours/day/ite
Upto now I have like 100 rows and 300 columns
in row 100 till 105 I like to sum/department the total planned hour
with formula sumif(A1:B100, "infra", c1:c100). I did some spot check
and founf that some, not all, calculations where wrong. I checke
against a simple SUM from column c which gave the correct calculation

Is there a limmit to sumif or does anybody else has a clue or othe
sollution

thanks in advance

To
 
Interesting that =SUMPRODUCT( (--(A1:B100="infra"), C1:C100) will not work
here since the two ranges of SUMPROCDUCT are unequal.
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address

Peo Sjoblom said:
No but sumif will only return hits from one column (Yoiu have 2, A & B), if
you need A1:B100 you can use

=SUMPRODUCT(--(A1:B100="infra")*(C1:C100))
Click to expand...
 
Interesting that =SUMPRODUCT( (--(A1:B100="infra"), C1:C100) will not work
here since the two ranges of SUMPROCDUCT are unequal. ...
Click to expand...
...

But that's to be expected, isn't it? Peo's formula using '*' rather than ','
makes implicit use of Excel's array extension semantics, e.g.,

{1,2;3,4}*{1;-1} = {1,2;-3,-4}

so in Peo's formula there's a single array argument to SUMPRODUCT. Note that
Peo's formula could give wrong answers. For example,

{0,0;1,0;0,1;1,1}*{1;2;3;4} = {0,0;2,0;0,3;4,4}

which would sum to 13, which exceed the sum of {1;2;3;4}. If the two columns
were to be considered *OR* criteria, it'd be necessary to use either

=SUMPRODUCT(((A1:A100="infra")+(B1:B100="infra")>0)*C1:C100)

or

=SUMPRODUCT(--((A1:A100="infra")+(B1:B100="infra")>0),C1:C100)

Now, personally, I believe this is a nonissue because I believe the OP had a
typo in his SUMIF formula and should have written

=SUMIF(A1:A100,"infra",C1:C100)

since s/he mentioned summing by department. In which case, the other response
branch in this thread is probably the right corrective - assume there are stray
trailing spaces in col A.
 
Many Thanks,

I didn't know sumif does not work with multiple columns.
I altered the sheet and now it calculates correct.

I will also try the sumproduct.

Regards,

Ton Kleimeier
 
...
...
I didn't know sumif does not work with multiple columns.
Click to expand...
...

It does, but in unexpected ways. What it doesn't do is provide anything like AND
or OR comparisons of multiple columns. Also, when SUMIF is given a multiple row
and multiple column range as it's first argument, when a third argument is used
it doesn't need to be the same shape/size as the first argument range. SUMIF
automatically expands it to the same size. Light testing shows that

=SUMIF(A1:B5,">3",C1:D5)

=SUMIF(A1:B5,">3",C1:C5)

=SUMIF(A1:B5,">3",C1)

all return the same result. It seems SUMIF(Rng1,Condition,Rng2) is equivalent to
SUMIF(Rng1,Condition,OFFSET(Rng2,0,0,ROWS(Rng1),COLUMNS(Rng1))).
 
Back
Top