Begineer with (seemingly) hard problem

[mrayner]

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 |
+-------------------------------------------------------------------+

 

[Arvi Laanemets]

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)

 

[Biff]

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]

Thanks so much guys!!
That would seriously have taken all day for me to figure out!

 

[mrayner]

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?

 

[Biff]

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]

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 an
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 Rayne

+-------------------------------------------------------------------
|Filename: Support Team Data Small.doc
|Download: http://www.excelforum.com/attachment.php?postid=3544
+-------------------------------------------------------------------

 

[Biff]

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