Looking for a sum or count formula

[karenbr]

I am trying to add up data based on another tab in my workbook using specific criteria. I have attached the file. The first tab lists demographic data by job title, job group,gender and ethnicity, but on then the next tab I need to total it by job group, gender and ethnicity. I was hoping to use a sum or count formula to auto-populate the second tab, but I can't get it to work.

Once the Job Group # column headings were named the same, this solved it:
E6 =SUMIF('Workforce Analysis'!$D:$D,MID($B6,1,255),'Workforce Analysis'!G:G)
M6 =SUMIF('Workforce Analysis'!$D:$D,MID($B6,1,255),'Workforce Analysis'!O:O)

[NBVC]

If you make the titles in Column A consistent or add a column in Workforce that categorizes each item in Column A as under the items in Column A of the Job Group sheet.. then you can use formula like this in E6:

=SUMIF('Workforce Analysis'!$A:$A,MID($A6,FIND(" ",$A6)+1,255),'Workforce Analysis'!F:F)

copied down and across the Male section,

Then in M6 use:

=SUMIF('Workforce Analysis'!$A:$A,MID($A6,FIND(" ",$A6)+1,255),'Workforce Analysis'!O:O)

copied down and across Female section.

[karenbr]

I wasn't able to get that to work, but I'm sure it's the way I have this set up. This may help: Workforce column D is the same data as Job Group column B so if I make their column headings the same, would that work?

[NBVC]

That is probably easier...

try in E6: =SUMIF('Workforce Analysis'!$D:$D,$B6,'Workforce Analysis'!F:F)

and in M6: =SUMIF('Workforce Analysis'!$D:$D,$B6,'Workforce Analysis'!O:O)