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

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 ***, 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<at>tarkon.ee )



"mrayner" <mrayner.1r9r6a_1119848701.4805@excelforum-nospam.com> wrote in
message news:mrayner.1r9r6a_1119848701.4805@excelforum-nospam.com...
>
> 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" <mrayner.1r9r6a_1119848701.4805@excelforum-nospam.com> wrote in
message news:mrayner.1r9r6a_1119848701.4805@excelforum-nospam.com...
>
> 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!

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?

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" <mrayner.1rb7ye_1119917108.2428@excelforum-nospam.com> wrote in
message news:mrayner.1rb7ye_1119917108.2428@excelforum-nospam.com...
>
> 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

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" <mrayner.1rboma_1119938707.2677@excelforum-nospam.com> wrote in
message news:mrayner.1rboma_1119938707.2677@excelforum-nospam.com...
>
> 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
>