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

  • Thread starter Thread starter SueQ
  • Start date Start date
S

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 :
 
SueQ > said:
I’d be really grateful if someone could assist me in how to “formula”
the below:
Click to expand...
....

See responses in microsoft.public.excel. Don't multipost.
 
Why make it difficult ?

Just put a COUNTIF() on each sheet (hide the row containing the cell i
necessary) and summarise those cells
 
BrianB > said:
Why make it difficult ?
Click to expand...

Because it's more fun.
Just put a COUNTIF() on each sheet (hide the row containing the cell if
necessary) and summarise those cells.
Click to expand...

Generally, hidden rows or columns tend to cause more problems than they
solve.

Note that the OP needs to conditionally count over 5 separate ranges on each
worksheet, and the OP wants to count the number of occurrences of 5
different words. That's 25 simple formulas per worksheet, all of which need
to be in the same cells on each worksheet in order to use simple formulas to
sum them, and there'd be 5 such totals. With 10 worksheets, that's 250
formulas plus 5 summary totals, one for each word, in order to keep things
simple as opposed to 5 complicated/difficult formulas. Profusion of simple
formulas can, on some occasions, lead to more problems than a few
complicated formulas. A 51-to-1 disparity may have reached the point at
which a bit of formula complexity would reduce overall complexity.
 
Hi Brian and Harlan

Just a quick note to let you both know that I have found a solution
that was just what I was after at my identical post at Excel - General
on this Forum and have pasted it below for yours and anyone else's
reference.

I apologise for my multiposting ... being new I wasn't sure which one
out of Miscellaneous and General that the question should have gone
into so I put it in both. Your point has been taken on board though,
many thanks :)

Once again, thank you both for your help, and a big thank you goes to
Max for the suggestion he made which has solved my problem. It is
working beautifully.




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)



All new readers are advised that the problem has been solved, but
thanks for taking the time to look.


Bye


Sue :)
 
Back
Top