Count number of yeses in a field - via pivot table

agwalsh used Ask the Experts™
hi Folks
I just want a pivot table to show me the number of yeses of up to date by department. I've attached a file. I'm wondering if I could do it via Calculated Field...
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Microsoft Excel Expert
Top Expert 2014
Professor JMicrosoft Excel Expert
Top Expert 2014

by the way your Antenatal Clinic count of yes is 5 not 4 as per your original data. please see uploaded attachment in my comment ID: 40868361
Most Valuable Expert 2011
Top Expert 2011
Create a pivot table, add department to both the Row and Values area, then add 'up to date' as a column field and filter it for Yes only. Job done. :)
The sample did not host a pivot table.
The solution can be done with a COUNTIFS function as shown in the attached.

Basically, COIUNTIFS(range1, value1, range2, value2,...).

COUNTIFS was added in Office 2007.  This files was an earlier version, but the function still works -- provided the Excel version is 2007 or newer.  (I used Excel 2013)


@Professor JimJam - sorry about the Antenatal count (what can I abacus was at the menders........) @richard daneke. not sure what you mean by "did not host a pivot table" but actually it did...did think of Countifs but I'm doing this for someone and they are new to Excel so while I could do a countifs - using a pivot table gives more flexibility.  
Did the job - thank you. As it happened in the actual data I had more fields and I found that adding the field to the Report Filter list also gave me what I was looking for.
As always, thanks to you all :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial