Summing on Multiple Criteria (Dynamic Range)

  • Thread starter Thread starter elumbukoodu
  • Start date Start date
E

elumbukoodu

Firstly Thanks Alladin for helping me out with the formulae .. .the
reports started working fine.

I got struck at this other point . . . Somebody please help me out!


My question now is . . .

Why does this not work. . .

=SUM(IF((EMPLOYEE="Jane")*(HOURS<10),AMOUNT))

and even this does not work . . .

=SUM((EMPLOYEE="Jane")*(HOURS<10)*AMOUNT)

(i just want to sum up the amount for Jane for when her hours were less
than 10)



but this would work perfectly fine

=SUMPRODUCT((EMPLOYEE="Jane")*(HOURS<10))

(here i'm just counting the number of times she worked below 10 hrs)



the range specifications for the dynamic ranges are as follows

EMPLOYEE
=x!$S$1:INDEX(x!$S:$S,LastRow)

LastRow
=MATCH(BigNum,x!$B:$B)

BigNum
=9.99999999999999E+307

HOURS
=x!$B$1:INDEX(x!$B:$B,LastRow)

AMOUNT
=x!$C$1:INDEX(x!$C:$C,LastRow)

Since the counting worked fine, i left the dynamic range names alone
and used totally different names (with similar formulae) for the
summing . . . it still does not work.

Somebody help me !!!
 
elumbukoodu said:
Firstly Thanks Alladin for helping me out with the formulae .. .the
reports started working fine.

I got struck at this other point . . . Somebody please help me out!


My question now is . . .

Why does this not work. . .

=SUM(IF((EMPLOYEE="Jane")*(HOURS<10),AMOUNT))

and even this does not work . . .

=SUM((EMPLOYEE="Jane")*(HOURS<10)*AMOUNT)

(i just want to sum up the amount for Jane for when her hours were less
than 10)



but this would work perfectly fine

=SUMPRODUCT((EMPLOYEE="Jane")*(HOURS<10))

(here i'm just counting the number of times she worked below 10 hrs)



the range specifications for the dynamic ranges are as follows

EMPLOYEE
=x!$S$1:INDEX(x!$S:$S,LastRow)

LastRow
=MATCH(BigNum,x!$B:$B)

BigNum
=9.99999999999999E+307

HOURS
=x!$B$1:INDEX(x!$B:$B,LastRow)

AMOUNT
=x!$C$1:INDEX(x!$C:$C,LastRow)

Since the counting worked fine, i left the dynamic range names alone
and used totally different names (with similar formulae) for the
summing . . . it still does not work.

Somebody help me !!!
Click to expand...

So why did you change from SUMPRODUCT to SUM?
Use this:
=SUMPRODUCT((EMPLOYEE="Jane")*(HOURS<10)*AMOUNT)
 
What

=SUMPRODUCT(--(EMPLOYEE="Jane"),--(HOURS<10))

does is a multiconditional count. While

=SUMPRODUCT(--(EMPLOYEE="Jane"),--(HOURS<10),AMOUNT)

gives a multiconditional summing. The latter is equivalent to what Paul
suggested. The difference is that the current syntax with comma's allows for
text values in AMOUNT like "". The -- bit is used to coerce arrays with
logical values into arrays of 1's and 0's, that is, into arrays with numbers
as expected by SumProduct. In Paul's version this coercion is done by the
first *.
 
Back
Top