Tidying up a formula

[Mediaid]

=SUM(COUNTIF(Wk1!$B$8:$F$11,A4),COUNTIF(Wk2!$B$8:$F$11,A4),COUNTIF(Wk3!$B$8:$F$11,A4),COUNTIF(Wk4!$B$8:$F$11,A4),COUNTIF(Wk5!$B$8:$F$11,A4))

As you can see, I want to see how many times the contents of A4 appear in a group of fields on a range of worksheets. Is there a way to shorten this formula as when it is finished i need wk6 wk7 wk8 etc etc through to wk30.

The formula is on the first work sheet, and there will be 30 subsequent worksheets so if it helps, i need that range of cells checking on every other worksheet.

Thanks in advance.

[6StringJazzer]

There are a couple of approaches to this, but there is not one trivial solution.

One approach is to put the COUNTIF formula on every sheet (let's say in G1), then use this

=SUM(Wk1:Wk30!$G$1)but the initial setup would be tedious. This method of referencing a range of worksheets works with SUM but unfortunately not with COUNTIF.
Another option is to write a user-defined function in VBA. I might be able to suggest something if you're interested in going that route.

[martindwilson]

you could put the same formula eg COUNTIF($B$8:$F$11,A4) in all cells g1 at once by selecting all sheets and typing it in g1 of first sheet it will do all the others as well then use =SUM(Wk1:Wk30!$G$1)