Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
Array formula returning wrong results | Excel Discussion (Misc queries) | |||
referencing the value of a cell containing an array formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |