How to apply countif formula when data are on different sheets

**akash kothari** · 07-22-2012, 11:05 AM

Hi, in the attached file i have prepared the format of employee attendace tracker. In the first sheet(leave details) i wanted to know how many leave for each employee is pending for the whole year, i have put the COUNTIF formula in cell E4 but it gives me error message "circular cell reference"

=COUNTIF(January!E4:AI4,"ML").=COUNTIF(January!E4:AI4,"ML")but this formula is only calculating only for January and i wanted to calculate for whole year.Leave details are on first sheet Shows number of maximum number of days an employee can take what leave. I am attached the file i prepared please let me know where i am making mistake. Thanks in advance.

**Richard Buttrey** · 07-22-2012, 11:43 AM

Hi,

I don't see a circular reference, but you'd be better off arranging your data in a single sheet and not a sheet for each month.

So create a sheet with columns for S.No, Employee Name, SID, Process, Date, Leave.

Now just enter your details putting "ML" or whatever other analysis codes you have in the end column and use the SUMIF.

In fact you may not need all those columns since presumably S.No and Employee name are the same relationship and you could just maintain a separate table with these two so that you can always use a VLOOKUP when you want to see one or the other in any results.

**akash kothari** · 07-22-2012, 12:58 PM

Hi Richard thanks for reply
Actually my manager asked me to update the current tracker which we are using is having same format as you are telling me, Employee name, ID, process, months (by freezing first 3 column) on the first sheet but its little difficult when an employee ask for leave or when we need to scroll through all the months to find in which month maximum leaves are allotted and their should be no more than 2 employee on same kind of leave , that's why i am considering this format this time. I am not sure how to add all months after jan, =countif(jan:feb:march!E4:AI4,"ML") or ???

**Richard Buttrey** · 07-22-2012, 01:57 PM

Hi,

I'm not sure what you are now asking for.

The one thing I am sure of is that whatever you are being asked to report, it will be far far easier to answer the question if you arrange your data on a single sheet as I suggested, and indeed as you're manager is confirming.

Do this first, then upload the amended workbook and manually add some typical results that you expect to see so that we can explain what functions/formulae you will need to use.