Simplify formula

[MiGon]

Hi Guys,
Is there any way to simplify formula below, please
I need to count how many times names occured for 3 shifts each day a week (new sheet is added each week)
Best would be use sheets index start from last one
Thanks
M

=((COUNTIF('[Rota 2022.xlsx]Wk beg 28112022'!B4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 28112022'!E4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 28112022'!H4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 28112022'!K4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 28112022'!N4,$G2))+((COUNTIF('[Rota 2022.xlsx]Wk beg 21112022'!B4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 21112022'!E4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 21112022'!H4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 21112022'!K4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 21112022'!N4,$G2))))+((COUNTIF('[Rota 2022.xlsx]Wk beg 14112022'!B4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 14112022'!E4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 14112022'!H4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 14112022'!K4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 14112022'!N4,$G2)))+((COUNTIF('[Rota 2022.xlsx]Wk beg 07112022'!B4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 07112022'!E4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 07112022'!H4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 07112022'!K4,$G2))+(COUNTIF('[Rota 2022.xlsx]Wk beg 07112022'!N4,$G2)))

 

[Legacy 143009]

You can use the formula below if counting C, D, F,G, I, J, L and M column is not a big deal. You should use another formula if you have to exclude them.

=COUNTIFS('[Rota 2022.xlsx]Wk beg 28112022'!B4:N4,$G2,'[Rota 2022.xlsx]Wk beg 21112022'!B4:N4,$G2,'[Rota 2022.xlsx]Wk beg 14112022'!B4:N4,$G2,'[Rota 2022.xlsx]Wk beg 07112022'!B4:N4,$G2)

 

[MiGon]

You can use the formula below if counting C, D, F,G, I, J, L and M column is not a big deal. You should use another formula if you have to exclude them.

=COUNTIFS('[Rota 2022.xlsx]Wk beg 28112022'!B4:N4,$G2,'[Rota 2022.xlsx]Wk beg 21112022'!B4:N4,$G2,'[Rota 2022.xlsx]Wk beg 14112022'!B4:N4,$G2,'[Rota 2022.xlsx]Wk beg 07112022'!B4:N4,$G2)

Hi,
That would create a mess as do not need count other columns.
One of my sheets looks like below

 

[Fluff]

How about

=SUMPRODUCT(('[Rota 2022.xlsx]Wk beg 28112022'!B4:N4=G2)*(MOD(COLUMN('[Rota 2022.xlsx]Wk beg 28112022'!B4:N4),3)=2))

& do the same for the other sheets & add them together.

 

[MiGon]

How about

=SUMPRODUCT(('[Rota 2022.xlsx]Wk beg 28112022'!B4:N4=G2)*(MOD(COLUMN('[Rota 2022.xlsx]Wk beg 28112022'!B4:N4),3)=2))

& do the same for the other sheets & add them together.

Hi Fluff,Thank you for the response, will go through formula, let you know how it works.
Thanks
M

 

[Fluff]

Ok

 

[MiGon]

Ok

Hi Fluff,
I cannot get this working, getting 0 as result as not familiar with this formula could you kindly put some light on it, please

=SUMPRODUCT(('[Rota 2022.xlsx]Wk beg 28112022'!B14:P14=G12)*(MOD(COLUMN('[Rota 2022.xlsx]Wk beg 28112022'!B14:P14),3)=2))

amended the formula but not sure where to start
Thank you
M

 

[Fluff]

The formula will count how many cells in cols B, E, H, K & N for row 14 match the value in G12.
If you are getting 0 then check that you don't have any leading/trailing spaces in any of those cells.

 

[MiGon]

Thanks a mill Fluff, you are star, i ve got it

 

[Fluff]

For C, F etc change the =2 to =3 & for the D, G etc use =1