COUNTIF Confusion

[tamedijodbro]

Hi guys,

I am hoping one of you wizards can help me out?

I am having trouble with the COUNTIF command! I work in a school and am trying to create stats on behaviour incidents that happen in school. I am trying to generate a breakdown to analyse by gender, and age but have this for each month.

I have 3 columns that I am working with: ENDER, YEAR GROUP and DATE.

The Year Group is already validated data from a list on another worksheet defined as REGGP (short for Register groups).
The date is entered by the teacher to state when the incident occurred.

I have managed to generate number for each month to count the number of MALES and FEMALES using the following formula. For instance November:

=SUMPRODUCT(--(D3:D33="Female"),--(F3:F33>=--"2012-11-01"),--(F3:F33<=--"2012-11-30"))

This counts the number of females and displays a number for November. I have this for each month.


Now here is where I am stuck.....


I now want to identify the number of S1 (year 1), S2, S3, S4, S5, S6 pupils that have had a discipline issue for that month. But the formulae I am using always generate a 0! I am using office XP which may be half the problem.

I have tried the following:

=COUNTIF(E3:E33,"2*")

but this counts all the S2 classes but I can't tell which S1 pupil was which month. Fine for an overall count though.

=SUMPRODUCT(--(E3:E33="=REGGP"),--(F3:F33>=--"2012-11-01"),--(F3:F33<=--"2012-11-30"))

I thought I could the definded name of my list but this didn't work either.

=SUMPRODUCT(--(E3:E33={"2A1","2A2","2D1","2D2","2M1","2M2"),--(F3:F33>=--"2012-11-01"),--(F3:F33<=--"2012-11-30"))

Instead of using the list I tried typing all S2 form classes but noting either.

But none of them are working?!?!?! Can anybody advise on how I need to tweak my syntax??? I'm sure it is my inexperience. This must be possible!

Many thanks!

[oeldere]

I think you get better help if you add your excel file, without confidentional information.

Please also add the desired result.

[jlax34]

Yeah, its tough to figure out exactly what you are trying to do from the explanation above. I'm wondering if you may be able to do something simple like using the "TEXT" function to convert the data into something easier and and then just using some sort of pivot table. As mentioned above, probably need an example sheet to determine this for sure.

[Palmetto]

Seems to me a Pivot Table would work for this.

[tamedijodbro]

BehaviourAnalysis_Model.xls

File attached. I appreciate a few people responding and thanks for your "Pivot Table" comments. Can you elaborate or direct me to a suitable tutorial?

Thanks again folks.

[daddylonglegs]

If you use this formula in N4

=SUMPRODUCT(--(LEFT($E$3:$E$33)=RIGHT(N$3)),--(TEXT($F$3:$F$33,"mmmm")=$J4))

you can copy across and down and it will work for the whole table

[tamedijodbro]

Thanks very much Daddy Long Legs. It does indeed work. Any chance you can explain the whole LEFT and RIGHT thing? also why the text value "mmmm"???

Thanks again. Have also tried a pivot table and it too looks like a possible method. The only thing is, because staff would input a date how could I get a monthly output as opposed to a set date?

Or, much like the online banking systems, can you choose a start date and an end date for analysis??? How would I do that in a pivot table?

Much appreciated everyone. It is amazing how powerful Excel can be. I have a reporting project that I'd also like to develop. The concept may be better suited to a database but I think Excel could pull it off!

[oeldere]

See a solution with an pivot table.

See the attached file.

[tamedijodbro]

oeldere thank you very much for you version of the table. It looks excellent however with the addition of the MONTH and YEAR column create too much info on the table. Is there a way of having these columns hidden but the pivot table still being able to generate monthly data?

Can it calculate this just from the DATE column instead???

Thanks again - I am really impressed by the kindness and knowledge of the member on this forum.

- JT

[oeldere]

Yes you can hide the column of month and year; and still get the data in the pivot table.

You can also show your pivot table on an own worksheet.

[tamedijodbro]

Thanks again everyone. Issue resolved. Got the worksheets to generate all the data/charts I needed.