sumproduct

J

jaypee

im using this formula:
=SUMPRODUCT((A2:A6="Male")*(B2:B6=21))

i want to change the rangeto A:A(all column A) and B:B but #NUM! always the
output.
what should i do?
 
B

Bob Phillips

SUMPRODUCT doesn't work with complete columns, you have to specify a range.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
A

aidan.heritage

You could get round the problem by using offset

=SUMPRODUCT((A2:OFFSET(A2,COUNTA(A:A)-1,0)="Male")*(B2:OFFSET(B2,COUNTA(A:A)-1,0)=21))
 
B

Bob Phillips

As long as there are no embedded blanks.

Better IMO aidan to defin dynamic ranges and use them, doesn't clog the
formula up.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
D

Dave Peterson

Sometimes, if you include lots of unnecessary cells, the formulas will slow
down.

My rule of thumb is to guess how many rows I need, then double it (then add a
bit more). It's not perfekt, though.
 
B

Bob Phillips

or use dynamic ranges (which is perfekt, sometimes <vbg>)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
J

jaypee

what do mean by dynamic range?
can you give example?
thanks
Bob Phillips said:
or use dynamic ranges (which is perfekt, sometimes <vbg>)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
Click to expand...
 
B

Bob Phillips

Aidan has already given an example

=SUMPRODUCT((A2:OFFSET(A2,COUNTA(A:A)-1,0)="Male")*(B2:OFFSET(B2,COUNTA(A:A)
-1,0)=21))

the OFFSET(A2,COUNTA(A:A)-1,0) part will ensure that the range looked at is
just as big as the data, as long as there are no blank rows embedded in the
data. He subtracts 1 because he is assuming the data starts in row 2, if row
3, subtract 2 etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

jaypee said:
what do mean by dynamic range?
can you give example?
thanks
Click to expand...
 
Top