Previous Topic Next Topic

Guide Me

Closed
SS - Jun 17, 2009 at 07:08 AM
 SS - Jun 17, 2009 at 11:23 PM
Hello,

I have 12 worksheets (jan to Dec), and i want to count the customer name repeated from jan to dec. for every month i will open on sheets and enter data. Now i want the result (counting the customer name from jan to dec) in another excel sheet of same workbook.

I tried the following formula.

=+SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&{1,2,3,4}&"!F1:F200"),"SYED"))

It works but when i add sheet 5, it doesn't work. so we have to update formula like this

+SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&{1,2,3,4,5}&"!F1:F200"),"SYED"))

for every time we can not do this, can u pls help.

Regards
David

2 responses

Answer 1 / 2
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Jun 17, 2009 at 07:26 AM
Hi SS
Add separately
use =if(iserror(COUNTIF(INDIRECT("Sheet"&{1}&"!F1:F200"),"SYED")) ,0,COUNTIF(INDIRECT("Sheet"&{1}&"!F1:F200"),"SYED")) +...for each sheet
0
Answer 2 / 2
SS
Jun 17, 2009 at 11:23 PM
Thanks for ur guidance.

I still need assistance. If i want to count a name in 1 or 2 sheets it will be ok.

But when i add additional sheets in workbook, the name in sheet should be counted.

The formula must count

Sheet1:Sheet12

On applying formulas, the additional sheet no 12 means it should be counted without any error.

Pls guide me.

Thanks

SS
0