sumproduct

[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?

 

[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)

 

[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))

 

[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)

 

[robert111]

why not use A1:A65534 instead of A:A

 

[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.

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[jaypee]

what do mean by dynamic range?
can you give example?
thanks

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[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)

what do mean by dynamic range?
can you give example?
thanks