pivot help please!

[tung256]

hello, im a secretary for a small school.
i have an attendance sheet for classes and the dates.
i would like to see Present count per day per classes. but for some reason, i can only do 1 date at a time.
in the file im sharing, you will see 3 tabs.
"all data" all the data i collected
"pivot" is where i can only do 1 date at a time.
"desired result" is an example of the pivot data i actually want.
please help! thanks for your time everyone
www.kerrsbuilding.com/test/example.xlsx

 

[sitewolf]

If I'm looking correctly, you're only getting one date because there's only one date included in your pivot table, yes?
If it's more than that, I'll try and look a bit more in a bit.

 

[tung256]

If I'm looking correctly, you're only getting one date because there's only one date included in your pivot table, yes?
If it's more than that, I'll try and look a bit more in a bit.

yes. if i try to put in additional dates. the pivot chart doesnt look anything like what i wanted.

 

[sitewolf]

If there's a better way, it's not rattling into my brain today, but my solution is- NO pivot table at all.

Shortest way to explain what I did to get what I think you want for a result is this:

1.) Copy your list of classes to a scratch sheet/file, sort, and remove duplicates so you have one listing of all classes
2.) Copy that deduped list to A4 of your desired result sheet so you have all classes listed there
3.) Type the formula below into cell B4 and copy it thru all dates, all lines (B4:F48 I believe)

=COUNTIFS('all data'!$A:$A,$A4,'all data'!F:F,"p")

to get more trick, if there would be more date columns ongoing that you'd want to add, we could add to that formula...as long as the headings both places matched...because right now the formula isn't looking at the date, just the actual column placement.

To explain what that formula is doing- the first part says to look in column A of the data sheet and find the class in that row on the desired results sheet, then the second part says to look only in those rows and count the P's.

 

[sitewolf]

My results page looks like this (not sure how this looks pasted here, but I'll give it a shot)

classes	09/12/16	09/13/16	09/14/16	09/15/16	09/16/16
ADV 101	0	0	4	6	5
ADV 102	4	4	3	4	0
ADV 103	3	5	3	4	2
ADV 201	0	0	0	9	7
ADV 202	0	0	0	8	0
ADV 203	0	4	6	7	5
ADV 301	0	0	9	7	9
ADV 401	0	0	13	7	8
ADV 402	14	13	12	13	13
AFRAMR HIS	6	7	5	7	2
ALGEBRA 1	11	13	11	14	11
ALGEBRA 2	6	4	8	8	8
ALGEBRA x	1	2	2	3	1
AMER. HIST	8	9	1	12	12
BIOLOGY	0	0	0	4	9
CAREER EXP	11	7	0	11	14
CHEMISTRY	0	0	0	10	8
COLLEGE PR	12	24	0	19	0
EARTH SCIE	0	0	0	5	3
ENG 1	2	1	4	5	1
ENG 2	1	3	1	2	1
ENG 3	3	2	5	1	8
ENG 4	4	4	3	3	3
ENGLISH 1	7	7	8	8	7
ENGLISH 2	5	3	3	7	3
ENGLISH 3	7	1	8	4	5
ENGLISH 4	3	3	4	3	2
ENGLISH x	4	4	4	4	3
GEOMETRY	12	6	10	11	8
GEOMETRY x	3	0	4	0	0
HEALTH	0	25	27	29	25
JRT	14	6	16	6	10
PHYS. ED	0	6	9	9	9
PHYS. ED.	0	19	19	18	19
PHYS. SCIE	0	0	0	11	16
PROBABILIT	0	2	0	1	0
PROJECT	10	8	8	9	0
science	0	0	0	0	1
SOCIAL SCI	5	10	5	13	4
SPANISH 1	17	15	23	16	21
SPANISH 2	11	9	12	18	7
SR. PROJ.	7	9	7	9	7
WRITING	0	0	0	0	0
WRLD HIST	0	0	3	0	0
WRLD HIST.	2	10	1	8	10

<tbody>
</tbody><colgroup><col><col><col span="4"></colgroup>

 

[tung256]

OMG! this is exactly what i wanted! thank you.
all this time a pivot is the solution thats why i tried but failed again n again. i guess i will use your suggestion! thanks sooo much for your time!