Array formula
 

Can anybody please help?

I'm trying to find out the following, How many 09-13 year olds enjoy
Football.

The spreadsheet is set up as column A, DOB. Column B formula to work
out Age in years, what sport you enjoy? in which there are varying
answers.

I can do a short array eg Gender & Sport, but because of the age
catergories it has completely foxed me.

Thanks

Clash:)


--
Clash
------------------------------------------------------------------------
Clash's Profile: http://www.excelforum.com/member.php...o&userid=18951
View this thread: http://www.excelforum.com/showthread...hreadid=572969

Array formula
 

Try something like:

=SUMPRODUCT(--(B1:B100)=9),--(B1:B100)<=13),--(C1:C100="Football"),--(D1:D100="Male"))

Change ranges/references to your requirements. You can change the literals
to cells containing the literals/numbers

=SUMPRODUCT(--(B1:B100)=X1),--(B1:B100)<=X2),--(C1:C100=X3),--(D1:D100=X4))

X1=9, X2=13 etc

Note SUMPRODUCT does not allow full columns i.e. B:B is invalid, and ranges
must be same dimensions.

HTH

"Clash" wrote:


Can anybody please help?

I'm trying to find out the following, How many 09-13 year olds enjoy
Football.

The spreadsheet is set up as column A, DOB. Column B formula to work
out Age in years, what sport you enjoy? in which there are varying
answers.

I can do a short array eg Gender & Sport, but because of the age
catergories it has completely foxed me.

Thanks

Clash:)


--
Clash
------------------------------------------------------------------------
Clash's Profile: http://www.excelforum.com/member.php...o&userid=18951
View this thread: http://www.excelforum.com/showthread...hreadid=572969

Array formula
 

Toppers Wrote:
Try something like:

=SUMPRODUCT(--(B1:B100)=9),--(B1:B100)<=13),--(C1:C100="Football"),--(D1:D100="Male"))

Change ranges/references to your requirements. You can change the
literals
to cells containing the literals/numbers

=SUMPRODUCT(--(B1:B100)=X1),--(B1:B100)<=X2),--(C1:C100=X3),--(D1:D100=X4))

X1=9, X2=13 etc

Note SUMPRODUCT does not allow full columns i.e. B:B is invalid, and
ranges
must be same dimensions.

HTH

"Clash" wrote:


Can anybody please help?

I'm trying to find out the following, How many 09-13 year olds enjoy
Football.

The spreadsheet is set up as column A, DOB. Column B formula to work
out Age in years, what sport you enjoy? in which there are varying
answers.

I can do a short array eg Gender & Sport, but because of the age
catergories it has completely foxed me.

Thanks

Clash:)


--
Clash




------------------------------------------------------------------------
Clash's Profile:
http://www.excelforum.com/member.php...o&userid=18951
View this thread:
http://www.excelforum.com/showthread...hreadid=572969



Cheers

Much appriciated.:)


--
Clash
------------------------------------------------------------------------
Clash's Profile: http://www.excelforum.com/member.php...o&userid=18951
View this thread: http://www.excelforum.com/showthread...hreadid=572969

Array formula
 

try this formula

=SUMPRODUCT((A2:A20<=13)*(B2:B20="f"))-SUMPRODUCT((A2:A20<9)*(B2:B20="f"))

modify to suiy you

"Clash" wrote in
message ...

Can anybody please help?

I'm trying to find out the following, How many 09-13 year olds enjoy
Football.

The spreadsheet is set up as column A, DOB. Column B formula to work
out Age in years, what sport you enjoy? in which there are varying
answers.

I can do a short array eg Gender & Sport, but because of the age
catergories it has completely foxed me.

Thanks

Clash:)


--
Clash
------------------------------------------------------------------------
Clash's Profile:
http://www.excelforum.com/member.php...o&userid=18951
View this thread: http://www.excelforum.com/showthread...hreadid=572969