Count/Counta HELP!

[steve]

Arghh!

I need to count records across a minimum of 55 worksheets
in the same Excel file and also specifying criteria for
the count.

E.g. 1/ Count all cells containing text "GE" from cells
m2:m65532 within Sheet1:sheet55....

and e.g. 2/ Count all cells containing text "GE" from
cells m2:m65532 within Sheet1:sheet55 when cells R2:R65532
contain text "FF"

Can anyone help please?

Thanks

Steve

 

[CLR]

=COUNTIF(M2:M65532,"ge") will give you the number of "ge" cells on a given
sheet, and

=SUMPRODUCT((M2:M65532="ge")*(R2:R65532="ff")) will give you the number of
rows that have both a ge" in column M and a "ff" in column R

put these two formulas in each sheet in the same two cells, and on your
master sheet, you can simply sum these cells
from all the sheets

hth
Vaya con Dios,
Chuck, CABGx3

 

[Steve]

NICE ONE! Thanks....

-----Original Message-----
=COUNTIF(M2:M65532,"ge") will give you the number of "ge" cells on a given
sheet, and

=SUMPRODUCT((M2:M65532="ge")*(R2:R65532="ff")) will give you the number of
rows that have both a ge" in column M and a "ff" in column R

put these two formulas in each sheet in the same two cells, and on your
master sheet, you can simply sum these cells
from all the sheets

hth
Vaya con Dios,
Chuck, CABGx3







.

 

[Dave R.]

Just in case you want to avoid summing up a formula you must put on 55
different sheets, there is a way to use sumproduct (or sum) across many
worksheets, and I think it involves INDIRECT. Though I don't know how to do
it off the top of my head, I'm sure you can find it on a google search with
words like 'excel indirect sumproduct worksheets' & maybe 'count'.