Count report

[CHML]

Hi,
Here is my scenario; For every session taught by a teacher, with at least one student participating, that is what counts. Here is part of a spreadsheet that shows students' attendance, each group with at least one "P" for that day, is considered a session and counts.

Now is my question; how can I get a report of the total sessions per group? (in this example each group has done 8 sessions)

 

[awoohaw]

in circled section, are you asking for count of all Ps from R2 to AA 12?

 

[awoohaw]

or are you asking for a count of all columns in R2:AA12 where there is at least one P in the column?

 

[CHML]

A count of DAYS that have at least one "P" per group. e.g. group 1STA = 8

Thanks

 

[awoohaw]

I'm not going to recreate you exact grid. but I think this is what you need from a formula perspective:

You may need to enter it with the CSE keystrok (CTRL-SHFT-ENTER)

mr excel questions 34.xlsm
								H										R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1								GROUP										1(Mon)	2(Tue)	3(Wed)	4(Thu)	5(Fri)	6(Sat)	7(Sun)	8(Mon)	9(Tue)	10(Wed)
2								1STA										P	P	P	P	P		C	P	P	P	1STA	8
3								1STA										P	P	P	P	P		C	P	P	P	2STB	8
4								1STA										P	A	P	P	P		C	P	P	P	1STB	8
5								1STA										P	P	P	P	P		C	P	P	P	2STA	8
6								1STA										P	A	P	A	P		C	A	P	P
7								1STA										P	A	A	P	P		C	P	P	A
8								1STA										P	P	P	P	A		C	A	P	P
9								1STA										P	A	P	P	P		C	P	P	P
10								2STB										P	P	P	P	P		C	P	P	P
11								2STB										P	P	A	P	A		C	P	P	P
12								2STB										P	P	P	P	P		C	P	P	P
13								2STB										P	P	P	P	P		C	P	P	P
14								2STB										P	P	P	P	A		C	P	P	P
15								2STB										P	P	P	P	P		C	P	P	P
16								2STB										A	A	P	P	A		C	P	A	P
17								2STB										P	P	P	P	P		C	P	P	P
18								1STB										A	P	P	P	P		C	P	P	P
19								1STB										P	P	P	P	P		C	A	P	A
20								1STB										P	A	P	P	P		C	P	P	P
21								1STB										P	P	P	P	P		C	P	P	A
22								1STB										A	P	P	P	P		C	P	P	P
23								1STB										A	P	P	P	P		C	P	A	P
24								1STB										P	P	P	P	A		C	P	A	P
25								1STB										P	P	P	P	P		C	P	A	P
26								2STA										P	P	P	P	P		C	P	P	P
27								2STA										P	P	A	P	P		C	P	P	P
28								2STA										P	P	P	P	P		C	P	P	P
29								2STA										P	P	P	P	A		C	P	P	P
30								2STA										A	A	P	P	P		C	P	P	P
31								2STA										P	P	P	P	P		C	P	P	P
32								2STA										P	P	P	P	P		C	P	P	P
33								2STA										P	P	P	P	P		C	P	P	P
CHML

Cell Formulas
Range	Formula
AC2:AC5	AC2	=(--(SUM(((--($H$2:$H$33=$AB2))*(--($R$2:$R$33="P"))))>0)) +(--(SUM(((--($H$2:$H$33=$AB2))*(--($S$2:$S$33="P"))))>0)) +(--(SUM(((--($H$2:$H$33=$AB2))*(--($T$2:$T$33="P"))))>0)) +(--(SUM(((--($H$2:$H$33=$AB2))*(--($U$2:$U$33="P"))))>0)) +(--(SUM(((--($H$2:$H$33=$AB2))*(--($V$2:$V$33="P"))))>0)) +(--(SUM(((--($H$2:$H$33=$AB2))*(--($W$2:$W$33="P"))))>0)) +(--(SUM(((--($H$2:$H$33=$AB2))*(--($X$2:$X$33="P"))))>0)) +(--(SUM(((--($H$2:$H$33=$AB2))*(--($Y$2:$Y$33="P"))))>0)) +(--(SUM(((--($H$2:$H$33=$AB2))*(--($Z$2:$Z$33="P"))))>0)) +(--(SUM(((--($H$2:$H$33=$AB2))*(--($AA$2:$AA$33="P"))))>0))

 

[CHML]

You're amazing, Awoohaw!! Thank you!

 

[awoohaw]

You're amazing, Awoohaw!! Thank you!

You're welcome, I'm pleased you found a solution on the Mr. Excel Forum.

If you believe one of the posts here has provided you with a solution, please mark that post as the answer.

Best Wishes!