Help required merging formula's into one formula

[Liverlee]

Hi

I have an annual leave planner with six teams. Originally named T1, T2, T3, T4, T5 and T6.

The formula below works to calculate the number of people on leave in team 1 if i filter to just team 1.

=COUNTIFS(E6:E30,"T1",F6:F30,"X")

this formula is written in cell F33

In cell f34 i repeat the formula changing T1 to T2
In cell f35 i repeat the forumla changing it to T2 to T3 etc etc.

but ideally i'd like to combine all the formula's below into one formula

=COUNTIFS(E6:E30,"T1",F6:F30,"X")
=COUNTIFS(E6:E30,"T2",F6:F30,"X")
=COUNTIFS(E6:E30,"T3",F6:F30,"X")
=COUNTIFS(E6:E30,"T4",F6:F30,"X")
=COUNTIFS(E6:E30,"T5",F6:F30,"X")
=COUNTIFS(E6:E30,"T6",F6:F30,"X")


so i can drag it across columns F to AJ.

and if i apply the filter to either team 1 or team 2 i still get the same results.

I'm presently having to write

in cell g33=COUNTIFS(E6:E30,"T1",G6:G30,"X")
In cell g34=COUNTIFS(E6:E30,"T2",G6:G30,"X")
in cell g35=COUNTIFS(E6:E30,"T3",G6:G30,"X")
in cell g36=COUNTIFS(E6:E30,"T4",G6:G30,"X")
in cell g37=COUNTIFS(E6:E30,"T5",G6:G30,"X")
in cell g38 =COUNTIFS(E6:E30,"T6",G6:G30,"X")

in cell h33=COUNTIFS(E6:E30,"T1",H6:H30,"X")
In cell h34=COUNTIFS(E6:E30,"T2",H6:H30,"X")
in cell h35=COUNTIFS(E6:E30,"T3",H6:H30,"X")
in cell h36=COUNTIFS(E6:E30,"T4",H6:H30,"X")
in cell h37=COUNTIFS(E6:E30,"T5",H6:H30,"X")
in cell h38 =COUNTIFS(E6:E30,"T6",H6:H30,"X")

surely there's an easy way?!

Please can someone help me out with this. I'm an excel novice.

 

[Aladin Akyurek]

E33 = T1
E34 = T2
E35 = T3
etc.

In F33 enter, copy across, and down:

=COUNTIFS($E$6:$E$30,$E33,$F6:$F30,"X")

 

[Marcelo Branco]

Try

Formula in F33
=COUNTIFS($E$6:$E$30,"T"&ROWS(F$33:F33),F$6:F$30,"X")
copy down until F38 and copy across

M.

 

[Liverlee]

Try

Formula in F33
=COUNTIFS($E$6:$E$30,"T"&ROWS(F$33:F33),F$6:F$30,"X")
copy down until F38 and copy across

M.

Thanks for trying.

Neither formula seems to work - i'm not sure what i'm doing wrong.

perhaps i'm missing is blank if there is no x.

 

[Liverlee]

Edit, sorry my mistake it works, thank you both -

 

[Aladin Akyurek]

E33 = T1
E34 = T2
E35 = T3
etc.

In F33 enter, copy across, and down:

=COUNTIFS($E$6:$E$30,$E33,$F6:$F30,"X")

That should be:

=COUNTIFS($E$6:$E$30,$E33,F$6:F$30,"X")