Solved How can I add text headings on multi sheets

November 30, 2014 at 01:08:20
Specs: Windows Vista
I am struggling to solve the following problem.
I have multi sheet spread sheet from which I am trying to add all the occurrences of a given text heading e.g. MEASHAM on two or more sheets but in the same column.
I can do this for ONE sheet using; =COUNTIFS(‘SHEET 1’!E:E,”MEASHAM”)
But cannot resolve more than one sheet.

See More: How can I add text headings on multi sheets

Report •

November 30, 2014 at 02:57:22
Perhaps create a specific template formatted that way? Not that "up" on Excel sheets etc., but the template approach generally works with Word so can't immediately see why not with Excel

Report •

November 30, 2014 at 19:45:23
✔ Best Answer
COUNTIF deos not allow 3D references and therefore cannot be used across multiple sheets.

There are (at least) 3 options:

1 - The Brute Force Formula Method:


2 - The More Elegant Formula Method:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A3&"'!E:E"), "Measham"))

where e.g. A1:A3 contain the Sheet Names for the sheets that you are interested in.

3 - The User Defined Function (UDF) method using VBA:

Function myCountIf(myRng As Range, myData) As Long
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        myCountIf = _
        myCountIf + WorksheetFunction.CountIf(ws.Range(myRng.Address), myData)
    Next ws

End Function

This UDF will COUNTIF across all sheets in the workbook. If you need to exclude some sheets there are ways to do that, but we would need more details.

Let us know which method works best for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

December 1, 2014 at 00:22:15
As I freely admit above, not very "up" (knowledgeable) re' Excel... However I can't see why it would not be simple(r) to make a template based on a sheet configured as required?

Enter the header(s) as required, save the sheet etc. in template form; load/use that template for new worsheets, and save accordingly as bog standard worsheets?

Appreciate I may be missing something here that prevents my suggestion working...

Report •

Related Solutions

December 1, 2014 at 02:20:32
The brute force method is long BUT IT WORKS thank you.....

Report •

Ask Question