Using SUMIF function

Resolved · High Priority · Version 2000

Fri 27 Feb 2009, 15:58 Delegate Juho said...

Juho has attended:
Excel VBA Intro Intermediate course

Using SUMIF function

I'm trying to use the SUMIF excel function. It works just fine in form:

=SUMIF(Total!C101:C141,">5",Total!B101:B141)

and in form

=SUMIF(Total!C101:C141,J22,Total!B101:B141)

but I cant get it working when I try to make the "bigger than" comparison with a cell reference. For example

=SUMIF(Total!C101:C141,">J22",Total!B101:B141)

returns zero even there are values in the cells and everything looks to be in order otherewise. What could be the problem?

Thanks in advance,

Juho

Mon 16 Mar 2009, 15:25 Trainer Carlos said...

RE: Using SUMIF function

Hi Juho

In the SumIf function the Criteria section is taken as a string value so in the wron equation yuo are looking for all cells greater than the WORD J22 NOT the value in it.

One way to get around this is to write >5 in cell J22 and then use your second example:

=SUMIF(Total!C101:C141,J22,Total!B101:B141)

In this way J22 contains the criteria >5 and gives you the correct answer.

I have attached a little example as a demonstration.

Hope this helps

Carlos