Excel Dups Unique

[Tarun.M]

I am having a day wise data sheets where in i want to know how many times a single customer has been called in a week or in a month (duplicates). I have the formula but if you can replace it with shorter one, b'coz if we go by this formula day wise it becomes large.

Formulla:

=COUNTIF(Sheet1!B:B,Sheet4!B:B)+COUNTIF(Sheet2!B:B,Sheet4!B:B)+COUNTIF(Sheet1!B:B,Sheet4!B:B)


I have no knowledge about macros. So if you can help me with formula only. That would be gr8.

 

[prasaddn]

Hi Tarun,


Have you tried Pivot Table ?


Regards,

Prasad

 

[SirJB7]

Hi, Tarun.M!

I guess that the last COUNTIF should be (Sheet3!B:B,Sheet4!B:B).

Besides, can you define how is your workbook and data structure in detail? Or upload a test file, if you consider it appropriate.

Regards!

 

[srinidhi]

Hi,


If all the data is in a single sheet Pivot is the best thing to do, if the data is in different tabs, you can use the 3D reference formula:


Ex:Assuming that the data is in tabs from Sheet1, Sheet3 & the count you want is in column B in all the sheets, use the below mentioned formula in sheet 3


=COUNTA(Sheet1:Sheet3!B2)