Create Pivot with worksheet that has split/complex column headers?

[sezza79]

Hi

I'm hoping someone may be able to help me. I need to analyse some data and I hoped that a Pivot Table may be the answer. Unfortunately, it doesn't like the fact I have split column headers and I was wondering if there was a way around it or another solution to save me time manually counting or filtering. I can't attach the actual spreadsheet for some reason but attach a screenshot to illustrate what I mean.

I need to count the number of 1's, 2's, 3's etc in each 'On Exit' column for each area. I then need to work out what percentage of the cohort they each are.

I would be grateful of any expert advice that may provide a quick solution!

Many thanks

Sarah

[cbatrody]

Hi,

Welcome to the Forum.

You can use COUNTIF to calculate the number of 1s, 2s etc in each column. If you need any further help please post a sample template in excel format.

[sezza79]

Hi there

Thanks for the suggestion of COUNTIF. I attach an example worksheet as requested so some further guidance on this would be gratefully received.

Is there no way to get a pivot table to work?

Many thanks

Sarah

[sezza79]

Could someone please advise on the COUNTIFS function as per previous post?

Many thanks!

[cbatrody]

Hi,

Please see the attached file.

[sezza79]

That is super helpful thanks!

A slightly more complex query - I need to be able to count the number of 1s, 2s and 3s etc for a specific section of the cohort, for example column M (FSM/PP), only those that are FSM or PP.

Is that possible?

Thanks in advance!

[cbatrody]

=countif(m$4:m$60,"fsm")
=countif(m$4:m$60,"pp")

[sezza79]

Thanks. This counts the number of FSM or PP individually, but I need to include both together, and work out from these, how many have 1s 2s or 3s?

[sezza79]

I can work out the number of fsm with a 1 for example by =countifs(m$4:m$60, "fsm", t$4:t$85, "1") but I need to add in the pp ones too?

[cbatrody]

=COUNTIFS(M$4:M$60, "fsm", T$4:T$85, "1")+COUNTIFS(M$4:M$60, "PP", T$4:T$85, "1")