how to solve #num error

**divi123** · 05-22-2013, 09:08 AM

Hi, I'm trying to sum data between specific date ranges and meeting a condition using SUMPRODUCT(). the forumlas works out in samples bt while working in actual sheet it shows a #num error.

Sheet attached, kindly help to resolve it.

**Tony Valko** · 05-22-2013, 09:12 AM

In Excel versions 2003 and earlier you can't use entire columns as range references in the SUMPRODUCT function.

Use smaller specific ranges.

**XOR LX** · 05-22-2013, 09:14 AM

Hi,

SUMPRODUCT will not work with whole rows or columns as its range references.

Regards

**Ron Coderre** · 05-22-2013, 09:17 AM

I think some simple changes to your formulas will work:

Using your posted workbook...on the Salary sheet...

Please Login or Register  to view this content.

Copy that formula across and down through cell I9

Is that something you can work with?

**divi123** · 05-23-2013, 01:06 AM

Hi All,

Thanks for your support. its done now.

**FDibbins** · 05-23-2013, 01:18 AM

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**divi123** · 05-23-2013, 01:24 AM

Could you help on this?

kindly suggest me for my following requirement.

I have opening leave balance with in the records and total leave availed during the current month, Required formulas for getting leave brk up of the availed leaves of the month meeting the availability of opening balance. I had almost derived at the formula. the problem which i'm facing now is

if there id 2.5 days of CL and 2 days of SL as opening balance and if an employee has availed 3 days of leave during the current month, mu formula split up shows it in the brk up as 2.5 days of CL and 0.5 days SL, which is inturn correct bt i dnt want a full day to be taken into split up of Half days. the desired result shud be of 2 CLs and 1 SL, then closing balance shud be 0.5 CL and 1 Sl in that case.. attached excel file for ready reference.

Shud i change my format of maintaining the time sheet, in such a way it meets the formula requirement? do suggest.

**divi123** · 05-23-2013, 01:26 AM

I wanted to create an attendance file for automating leave break up into CL & SL, meeting the certain conditions listed below.

1. an employee is eligible for 1 CL credit per month pertaining to Date of joining, whereas attendance is calculated on regular months.
ex. if an employee joins by 15th April 2013, he is eligible for 1 day leave till 15th May 2013, post to which he is eligible for additional 1 day credit for his 2nd month.

during the attendence period for 1st May to 31st may, he has an opening balance of 2days leave. say if he avails 2days leave on 3rd May 2013 and 14th May 2013, 1 day leave should be reduced from the opening balance and 1 day should be reflected in Loss of pay (LOP) and he should have balance 1 day leave as closing balance which could be adjusted in his 2nd month of joining (i.e) for the leaves he avails after 15th May.

In Simple, i required a formula where leaves should be counted from two different range meeting Date of joining as condition.
that is. to count leaves taken before the completion on 1month (from DOJ) and after completion

Sheet attached for the same. Can anybody help in solving my query, Seeking urgent revert on the same.