sum & sumif questions

[Ice5]

Sum works properly in the following instance:

=sum(j8:m120)

This sums all the values in between those two cells.

However in a sumif function:

=sumif(b8:b120,CONDITION,j8:m120)

I get some whacked out answer. It's not the sumer of the cells. Why does that (j8:m12) work in the sumif, but does work in the sum function?

Thanks

 

[Al Chara]

Confused as to what you are trying to do. Please clarify.

 

[Ice5]

The condition is ">"&today()-30*x where x is the number of months.

Well this is for a pilot log book

Column B contains dates
Columns J through M contain flight times.

I have an equation to total the times for the past x months (1, 6, & 12).

The following works and returns the correct totals:

=SUMIF(B7:B9999,">"&TODAY()-30,J7:J9999)
+SUMIF(B7:B9999,">"&TODAY()-30,K7:K9999)
+SUMIF(B7:B9999,">"&TODAY()-30,L7:L9999)
+SUMIF(B7:B9999,">"&TODAY()-30,M7:M9999)

Howerver this doesn't work:

=SUMIF(B7:B9999,">"&TODAY()-30,J7:M9999)


EDIT: Formatting
This message was edited by Ice5 on 2002-04-01 12:56

 

[Al Chara]

When you enter your formula, are you entering it by pressing Ctr+shift+enter instead of just enter. Look up how to enter an array formula (CSE formula).

 

[Ice5]

I hadn't tried CTRL + SHIFT + ENTER.

I tried it now however, and same deal.

i.e.

=SUMIF(B8:B10000,">"&TODAY()-30*6,J8:M10000)

Still does not return the correct value (it returns 0.9 instead of 21.5)
This message was edited by Ice5 on 2002-04-01 13:04

 

[Chris Davison]

Ice,

your ranges are unequal size....

see in your individual sumifs, they are all equal in size so individually you get correct results

 

[Aladin Akyurek]

On 2002-04-01 12:53, Ice5 wrote:
The condition is ">"&today()-30*x where x is the number of months.

Well this is for a pilot log book

Column B contains dates
Columns J through M contain flight times.

I have an equation to total the times for the past x months (1, 6, & 12).

The following works and returns the correct totals:

=SUMIF(B7:B9999,">"&TODAY()-30,J7:J9999)
+SUMIF(B7:B9999,">"&TODAY()-30,K7:K9999)
+SUMIF(B7:B9999,">"&TODAY()-30,L7:L9999)
+SUMIF(B7:B9999,">"&TODAY()-30,M7:M9999)

Howerver this doesn't work:

=SUMIF(B7:B9999,">"&TODAY()-30,J7:M9999)


EDIT: Formatting
This message was edited by Ice5 on 2002-04-01 12:56

SUMIF requires ranges of equal size. Thus,

=SUMIF(B7:B9999,">"&TODAY()-30,J7:M9999)

cannot replace your larger formula that adds up the results of independent SUMIFs.

If you insist on a single formula, use:

=SUMPRODUCT((B7:B9999>TODAY()-30)*(J7:M9999))

Aladin

 

[Ice5]

Oh, neat.

Thanks Aladin.

I don't REQUIRE only one formula, but otherwise I'd have 10 separate forumla's. Just ugly and hard to work on.

the SUMPRODUCT function works out perfectly though, thanks.