Count IF & IFS function...help!!

[alan1988]

Hi, I have been ripping my hair out trying to figure out how to find a formula with the data below:

I need to show of the 29 staff who are Grade 4, what the total FTE is of anyone who is Grade 4 and the total FTE

Could someone show me what the best formula would be to calculate this?

Thanks in advance

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]0.8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]0.8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]0.8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]0.8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]0.8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade 4[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jproffer]

You mean this? :

Excel 2013/2016

	A	B	C	D	E

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]24.1[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]0.9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]0.4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]0.4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]0.4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]0.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]0.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]0.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]0.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]0.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Grade 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E1[/TH]
[TD="align: left"]=SUMIF(B1:B29,"Grade 4",C1:C29)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



I was assuming FTE was column C.

If that's not what you meant, I don't understand...they're all Grade 4, so a simple SUM(C1:C29) would do the trick, but I'm assuming that in your real data, some of them are not Grade 4...??