counting among several sheets

  • Thread starter Thread starter Louis
  • Start date Start date
L

Louis

I want to count how many times "Payment Required" shows up
for several sheets. It would always be in cell L12. I
would want this formula in on the Totals sheet.

Thanks

Louis
 
One way

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MyNames&"'!"&CELL("address",L12)),"*Payment
Received*"))

where MyNames is a named range that holds a list of your sheet names,
if it is always in L12 you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MyNames&"'!L12"),"*Payment Received*"))

If the sheet names are excel default you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:10"))&"'!L12"),"*Payme
nt Received*"))


ROW(INDIRECT("1:10"))

is the sheet numbers as in Sheet1, Sheet2 up to 10, change to fit, if
different names
use the first formula where you list all sheet names in question and use a
defined name (MyNames)
 
-----Original Message-----
One way

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MyNames&"'!"&CELL ("address",L12)),"*Payment
Received*"))

where MyNames is a named range that holds a list of your sheet names,
if it is always in L12 you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MyNames&"'! L12"),"*Payment Received*"))

If the sheet names are excel default you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT ("1:10"))&"'!L12"),"*Payme
nt Received*"))


ROW(INDIRECT("1:10"))

is the sheet numbers as in Sheet1, Sheet2 up to 10, change to fit, if
different names
use the first formula where you list all sheet names in question and use a
defined name (MyNames)

--

Regards,

Peo Sjoblom





.
I don't quite follow your answer. Here's more
Click to expand...
information. The first sheet i want to include is named
Adams and the last sheet is named Warkentin. The sheet
before all these sheets is named Total and thats where I
want the formula to count how many times "Payment
Required" is listed in cell L12.

Thanks

Louis
 
Hi Louis

first create on your target sheet a list with all the spreadsheet names
you want to use in your formula. e.g. use column a and start entering
the sheet names in A1:A10

Now select this range and goto 'Insert - Name - Define'. Use the name
'MyNames' for this range.
Now use Peo's first formula
 
Back
Top