Comparing, counting, if, sum... help...

[LisaM]

I have a spreadsheet of student data and I want to analyse data in four
columns in particular. The columns identify school terms and contain numeric
values which identify a school subject code. (Codes run from 1-15).

I need to count each subject a student studied over the four terms and I can
do this easily when the codes refer to single subjects. (I had help from
T.Valko and Teethless Mama - thank you both!)

However, when I went back to double check the data I realised that there was
a complication.

Codes 1 to 4 identify four different subjects, while codes 5 to 15 represent
various combinations of those four subjects. e.g. Code 5 means the student
studied subject codes 1 and 4; code 7 equals subjects 3 and 4, and 6 equals
subjects 1, 2 and 4, and so on for the rest of the codes.

The data looks something like this in the spreadsheet:

Term 1-----Term 2-----Term 3-----Term 4
1 ------------ 1 ---------- 4 ----------10
0 ------------ 1 -----------1 ---------- 1
10----------- 2 -----------1 -----------6
4 ------------ 0 -----------7 -----------1

Is there a way to count each subject only once? And can it be done in Excel
2003?

Thank you.

LisaM


(This was my original query on funtions -
http://www.microsoft.com/office/com...=en-us-excel&lang=en&cr=US&sloc=en-us&m=1&p=1)

 

[Jason]

If you're counting each subject I'd assume you are using something like:

=COUNTIF(A110,1)

which will count all instances of subject code 1. Now if some other
codes represent multiple subjects then you just include this in the countif.
So, if code 5 represents subject codes 1 and 4, then when you're
counting subject codes 1 and 4 you need to add the count of subject code
5. You also mention code 6 represents subject codes 1,2 and 4. So, to
count all instances of subject code 1 use the following:

=COUNTIF(A110,1)+COUNTIF(A110,5)+COUNTIF(A110,6)

which simply counts all instances of the subject codes that represent
subject 1 and adds them together ---> Add a countif for each code that
represents the subject being counted.

Jay
__

 

[LisaM]

Thanks Jay!

I should have made it more clear that I don't want to count each instance
of, for example, code 1, whether it appeared as a 1 or as included in code 5,
only unique values.

However, I managed to use a formula to count cells where I'd already broken
the codes down into the subjects they represented, so I have the result I was
looking for after all.

Thank you for your help, anyway.

Formulae I can manage, but trying to get my head clear on functions, nesting
functions, making sure all the arguments are included (and how they can be
part of arguments for other nested functions) and that I haven't forgotten a
bracket somewhere - phew! Head spinning!!

 

[LisaM]

Just in case anybody's interested in how I finally got to count each subject
only once (rather than each instance of it) this is what I did.

Each subject was represented by a code, but I had also given codes to
instances where a student had studied two or more subjects in the same term.
For Subject 1, for instance, the code was 1, but if a student had studied it
along with Subject 2 the code was 5. And so on for other combinations.

I set up Cols E, F, G, H (one for each subject) and named them "# Terms Sub
1/2/3/4 studied" and inserted the following function (the same one Jason
suggested):

=countif(A22,1)+countif(A22,5)+..... etc

.... where Cols A through D were the four terms in the school year. All I
needed to do was change the value being searched for in each of the "# Terms"
columns. I then copied the function down through all the rows of data.

Finally, in Col I, I inserted a function to count how many of the columns E
through H had values greater than 0:

=countif(E2,">0")+countif(F2,">0")+countif(G2,">0")+countif(H2,">0")

and copied the function down through all the rows of data.

Job done!