Help required merging formula's into one formula

Liverlee

New Member
Joined
Nov 8, 2018
Messages
33
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.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,409
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

New Member
Joined
Nov 8, 2018
Messages
33
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,837
Messages
5,598,378
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top