Statiscal information

**Magikality** · 07-31-2011, 10:19 AM

Hi all,
I am trying to work out a formula that really has two variables. What it is, is a stastical report of attendance, to explain it better I will show a short diagram below.

*** Under 12 over 12 attendance 1 attendance 2 attendance 3
f 1 1 1 1 (3)
m 1 1 1 (2)

So this is a basic attendance list that has over 250 people and their attendance records, so what I need to calculate is:

Total number of females
Total number of males
Total number of over 12s
Total number of under 12s

I am sure one calculation would serve both female/male as well as under/over 12s.

The long explanation is that if they are female and attended 3 times it would put 3 in the total but as there is more than one female I need the total number of females, times the attendance record total along the rows. I thought of using a =countif(a:a,"f")*b:b, using a large some of females and males, but this just gave me a total calculating the number of females and multiplying it by the first number in the second column. I hope this makes sense. What about the IF statement, could that be helpful???

Thanks any help appreciated
Sharon

**6StringJazzer** · 07-31-2011, 11:04 AM

Your explanation was pretty clear right up to that last paragraph (long explanation). It describes something different than what you are saying in the list of four things you need.

Total number of females =COUNTIF(A:A,"f")
Total number of males =COUNTIF(A:A,"M")
Total number of over 12s =SUM(C:C)
Total number of under 12s =SUM(B:B)

Because those answers are so simple, maybe your description of those four items is not detailed enough.

Can you explain what the 3 different attendance columns mean? Does each row correspond to a single person, or some aggregation?

Do you want to know something like: the total number of attendances for rows that are both female and under 12, and that number for each row is the total of the three attendance columns?

**Magikality** · 07-31-2011, 07:44 PM

Originally Posted by 6StringJazzer

Your explanation was pretty clear right up to that last paragraph (long explanation). It describes something different than what you are saying in the list of four things you need.

Total number of females =COUNTIF(A:A,"f")
Total number of males =COUNTIF(A:A,"M")
Total number of over 12s =SUM(C:C)
Total number of under 12s =SUM(B:B)

Sorry I guess it is really hard when you can't see the work book. The worksheet, says a childs name then it counts them each time they attend, totalling them up even school term. Then I want the formula to calculate that if they are female and they attended at all in the school term then to add 1 to the total number of females. and so on for each variable (e.g. then males) etc.

So I guess in basic terms, it should ask are they female (yes), did they attend (and the amount of times doesnt matter) then (yes), add 1 to the total number of females. If they are not female and they attended then it should assume they are male and add 1 to the total number of males.

Does that make more sense?

Thanks
Sharon

**6StringJazzer** · 08-06-2011, 01:41 PM

I tried to figure out how to do this with SUMPRODUCT or an array formula but came up dry, so I added a column to total the attendances.

See attached.