Help with "SUMIFS" formula

[jpoppet]

Hi all

I'm creating a database for employee's attendance. I have created a sheet called "Raw Overview" whereby the totals of sickness/holiday etc are calculated according to the employee and month.

The code I currently have is

Please Login or Register  to view this content.

but it returns "#VALUE!"
'Employee' is a range I made just by ctrl + shift + down arrow in the column, same with 'date' but I am constantly adding data - is there anywhere I can tell excel to rename the range when a new field is added?

The information will be pulled from worksheet "Raw Data" where new entries are added daily using a userform, so I'm finding it hard defining where excel needs to look.
"Raw Data" sheet Raw Data to use.PNG
"Raw Overview" sheet Overview.PNG

I have attached the workbook for any who can help me.
Attendance Master - EXCEL HELP.xlsm

SO:
1. Can somebody confirm that the above formula I am using is correct? It seems to work but I can't copy and paste it. It should be pulling data from the dates between 1st jan and 31st.
2. Can excel rename a range after it adds a new row of data to it?

[daffodil11]

The syntax of the function is incorrect.

=SUMIFS('Raw Data'!E2:E1048576,date,">1/01/2013,<31/01/2013",Employee,'Raw Overview'!A3)

It should be:

SUMIFS(sum range, range 1, criteria 1, range 2, criteria 2, etc)

If you want to declare a date range, you need to make it two separate conditions. Also, it's more reliable to break the operators out from the value you're comparing via concatenation.

=SUMIFS('Raw Data'!E2:E1048576,date,">1/01/2013,<31/01/2013",Employee,'Raw Overview'!A3)

This would more accurately be translated to

=SUMIFS('Raw Data'!E2:E1048576,date,">"&"1/01/2013",date,"<"&"31/01/2013",Employee,'Raw Overview'!A3)

[jpoppet]

Thank you - just to be clear can you confirm this is correct?

Please Login or Register  to view this content.

= Add the total of cells between E2 and E1048576 in sheet 'Raw Data', if the date of the field in range 'date' is between the dates of 01/01/2013 and 31/01/2013

This will help me understand for future ref :-) What do you mean when you referance 'Operators' ?

[amit.wilson]

J - on your second question, you can create a dynamic range, i.e. the range size changes as new information is added. See this link: http://support.microsoft.com/kb/830287

Cheers

[daffodil11]

I meant the breaking out of the < or > compared to what is being evaluated.

For example, =countif(A1:A10,">5") works sometimes, but other times it will also look for cells that literally have ">5" in them. By breaking out the ">" and concatenating it with the &, you tell Excel that to do something with > instead of just look for it.

As for searching 1,048,576 rows of data to do your calculations, you might find this to be extremely burdensome on the processing speed once you have more than a couple of dozen of these formulas. Converting your range to a table would allow your formulas to automatically adjust as new lines of data are added, instead of telling it upfront to always searching a million rows.