|
Begineer with (seemingly) hard problem
Hi Guys, Can someone please help me out with this problem. In the spreadsheet there are 2 main colums I need to count. I have a set of results tables on the right that are lacking results :) So in Q5 I need a function that checks that the subject is a male, and they have Aspergers... count 1, then goto the next row and keep counting down the entire spreadsheet. Then in S5, obviously count all the females with Aspergers etc. etc..... I know it is probably easy and I should learn myself but if someone can tell me the first function or 2 that'd be fantastic. I just don't have time to learn anything more than I allready know about Excel at the moment. I promise I will learn and hoepfully help other people on this forum in the future... I have attached a renamed, waaayyy cut down version to with post. ((((I renamed the file to doc just so i could attach it to this post, you will have to rename it..)))) Thanks! - Mark +-------------------------------------------------------------------+ |Filename: Simple Ver.doc | |Download: http://www.excelforum.com/attachment.php?postid=3540 | +-------------------------------------------------------------------+ -- mrayner ------------------------------------------------------------------------ mrayner's Profile: http://www.excelforum.com/member.php...o&userid=24656 View this thread: http://www.excelforum.com/showthread...hreadid=382382 |
Hi
A bit too little info about your setup, but I'll try to help anyway. Let's assume you have data in range A2:F100 on sheet Table. In column C is info about sex, p.e. "M" or "F". In column E is info about Asperges (whatever it may be!) - TRUE or FALSE. Then the formula to count all males with Asperges will be: =SUMPRODUCT(--(Table!$C$2:$C$100="M"),--(Table!$E$2:$E$100=TRUE)) (Edit the formula to adjust it to your worksheet setup) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "mrayner" wrote in message ... Hi Guys, Can someone please help me out with this problem. In the spreadsheet there are 2 main colums I need to count. I have a set of results tables on the right that are lacking results :) So in Q5 I need a function that checks that the subject is a male, and they have Aspergers... count 1, then goto the next row and keep counting down the entire spreadsheet. Then in S5, obviously count all the females with Aspergers etc. etc..... I know it is probably easy and I should learn myself but if someone can tell me the first function or 2 that'd be fantastic. I just don't have time to learn anything more than I allready know about Excel at the moment. I promise I will learn and hoepfully help other people on this forum in the future... I have attached a renamed, waaayyy cut down version to with post. ((((I renamed the file to doc just so i could attach it to this post, you will have to rename it..)))) Thanks! - Mark +-------------------------------------------------------------------+ |Filename: Simple Ver.doc | |Download: http://www.excelforum.com/attachment.php?postid=3540 | +-------------------------------------------------------------------+ -- mrayner ------------------------------------------------------------------------ mrayner's Profile: http://www.excelforum.com/member.php...o&userid=24656 View this thread: http://www.excelforum.com/showthread...hreadid=382382 |
Hi!
In Q5: =SUMPRODUCT(--(C$4:C$20="M"),--(H$4:H$20=P5)) In S5: =SUMPRODUCT(--(C$4:C$20="F"),--(H$4:H$20=P5)) Copy both formulas down to row 25. If I were you, I would only list the conditions once. Sort of like this: ....................Males..............Females A ADHD AU B BI Biff "mrayner" wrote in message ... Hi Guys, Can someone please help me out with this problem. In the spreadsheet there are 2 main colums I need to count. I have a set of results tables on the right that are lacking results :) So in Q5 I need a function that checks that the subject is a male, and they have Aspergers... count 1, then goto the next row and keep counting down the entire spreadsheet. Then in S5, obviously count all the females with Aspergers etc. etc..... I know it is probably easy and I should learn myself but if someone can tell me the first function or 2 that'd be fantastic. I just don't have time to learn anything more than I allready know about Excel at the moment. I promise I will learn and hoepfully help other people on this forum in the future... I have attached a renamed, waaayyy cut down version to with post. ((((I renamed the file to doc just so i could attach it to this post, you will have to rename it..)))) Thanks! - Mark +-------------------------------------------------------------------+ |Filename: Simple Ver.doc | |Download: http://www.excelforum.com/attachment.php?postid=3540 | +-------------------------------------------------------------------+ -- mrayner ------------------------------------------------------------------------ mrayner's Profile: http://www.excelforum.com/member.php...o&userid=24656 View this thread: http://www.excelforum.com/showthread...hreadid=382382 |
Thanks so much guys!! That would seriously have taken all day for me to figure out! -- mrayner ------------------------------------------------------------------------ mrayner's Profile: http://www.excelforum.com/member.php...o&userid=24656 View this thread: http://www.excelforum.com/showthread...hreadid=382382 |
Well I've set it up, and is working great, except for one problem... Some kids have more than one disorder... So in the column "Special & Additional Needs" somtimes there are two conditions... ie. ADHD LN So when it tried to match it against N4 which says just ADHD, it won't match.. Without making another column for a second disorder, is there a symbol I can put between the disorders so that it will still recognide ADHD or LN seperatly? -- mrayner ------------------------------------------------------------------------ mrayner's Profile: http://www.excelforum.com/member.php...o&userid=24656 View this thread: http://www.excelforum.com/showthread...hreadid=382382 |
Hi!
If I recall correctly, there are some conditions that will cause a problem using the standard ISNUMBER SEARCH routine. Can you post an updated sample file? Biff "mrayner" wrote in message ... Well I've set it up, and is working great, except for one problem... Some kids have more than one disorder... So in the column "Special & Additional Needs" somtimes there are two conditions... ie. ADHD LN So when it tried to match it against N4 which says just ADHD, it won't match.. Without making another column for a second disorder, is there a symbol I can put between the disorders so that it will still recognide ADHD or LN seperatly? -- mrayner ------------------------------------------------------------------------ mrayner's Profile: http://www.excelforum.com/member.php...o&userid=24656 View this thread: http://www.excelforum.com/showthread...hreadid=382382 |
Here is an updates document with kids that have more than one disorder. At the moment there is just a few spaces between each disorder and therefore it will not count them in the results. Not sure what I am best to do.. Thanks, * the files is renamed to doc once again.... - Mark Rayner +-------------------------------------------------------------------+ |Filename: Support Team Data Small.doc | |Download: http://www.excelforum.com/attachment.php?postid=3544 | +-------------------------------------------------------------------+ -- mrayner ------------------------------------------------------------------------ mrayner's Profile: http://www.excelforum.com/member.php...o&userid=24656 View this thread: http://www.excelforum.com/showthread...hreadid=382382 |
Hi!
OK..... In column H just continue to enter the data like you are. If you need to enter more than 1 condition per cell keep separating them using a space. Don't use commas or semi-colons or any type of punctuation symbol. Formula in U3: =SUMPRODUCT(--(C$4:C$500="M"),--(ISNUMBER(SEARCH(" "&N3&" "," "&H$4:H$500&" ")))) Formula in W3: =SUMPRODUCT(--(C$4:C$500="F"),--(ISNUMBER(SEARCH(" "&N3&" "," "&H$4:H$500&" ")))) Biff "mrayner" wrote in message ... Here is an updates document with kids that have more than one disorder. At the moment there is just a few spaces between each disorder and therefore it will not count them in the results. Not sure what I am best to do.. Thanks, * the files is renamed to doc once again.... - Mark Rayner +-------------------------------------------------------------------+ |Filename: Support Team Data Small.doc | |Download: http://www.excelforum.com/attachment.php?postid=3544 | +-------------------------------------------------------------------+ -- mrayner ------------------------------------------------------------------------ mrayner's Profile: http://www.excelforum.com/member.php...o&userid=24656 View this thread: http://www.excelforum.com/showthread...hreadid=382382 |
| All times are GMT +1. The time now is 11:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com