Using COUNTIF (specific cells over multiple sheets) … can anyone help?

[SueQ]

Hi

I’d be really grateful if someone could assist me in how to “formula
the below:


I have twelve identically formatted worksheets (“A” through to “J”)

In each of the cells B6:F6, B10:F10, B14:F14, B18:F18 & B22:F22 on eac
sheet there will be a different one of 5 words (each word can and wil
occur more than once within the above range). For the purpose of thi
exercise I need to ignore the text in the other rows (eg 7-9, 11-1
etc).

What I need to tally on a separate sheet within the workbook is ho
many times each of the five words occur in the group above (eg B6:F6
B10:F10, B14:F14, B18:F18 & B22:F22) taking into account all 12 sheet
: ie, I need a grand total for each word.


Any suggestions would be sincerely welcomed though I’d probably nee
them to be fairly simply explained as I’d only consider myself a basi
to intermediate Excel user.

Thanks in advance


Sue :

 

[Max]

Try this simple set-up in a new sheet, named say: Summary

List your 5 words down in A2:A6

List across in row1:
- the sheet names "A" to "J" in B1:K1
- a label "Total" in L1

Put in B2:

=COUNTIF(INDIRECT(B$1&"!$B$6:$F$6"),$A2)+COUNTIF(INDIRECT(B$1&"!$B$10:$F$10"
),$A2)+COUNTIF(INDIRECT(B$1&"!$B$14:$F$14"),$A2)+COUNTIF(INDIRECT(B$1&"!$B$1
8:$F$18"),$A2)+COUNTIF(INDIRECT(B$1&"!$B$22:$F$22"),$A2)

Copy B2 across to K2, then copy down to K6

Put in L2: =SUM(B2:K2)

Copy L2 down to L6

The above should return the summary desired

If the text in the intervening rows 7-9, 11-13, etc
are *other* than your 5 listed words,
then the formula in B2 can be shortened to just:

=COUNTIF(INDIRECT(B$1&"!$B$6:$F$22"),$A2)

 

[SueQ]

Hi Max

Thanks for your quick reply. I'm going to print it out and then see
how I go with it ... will certainly let you know as soon as I've tried
it. Thanks again


Sue

 

[Max]

You're welcome, Sue.
Hope it gives what you're after.

 

[SueQ]

Hi Max and Harlan

Apologies for taking so long to get back to you both but I have only
not long ago had a chance to get back to the worksheet.

Max, thanks ever so much for your suggestion ... it worked beautifully
and was just what I was after - many, many thanks for taking the time
to assist.

And Harlan, I had already completed what Max had suggested before
reading yours, so I haven't tried your suggestion but I am grateful to
you too for your offer of assistance.

For any future readers, my problem has been solved but ta anyway for
taking the time to look.

All the best


Sue

 

[Max]

SueQ, glad to hear that !
Thanks for the feedback