Problem 1: SUMIF formula returns an error

However, when I try and use SUMIF to collect data from a sheet after Sheet 1, I get a VALUE error.

My workbook is set up as follows:

Sheet 1, which is named Events, has record numbers in rows 2:467775 in column A. In column W in row 2, I'm trying to collect data from Sheet 4, which is named NM. NM has record numbers in column A in rows 2:20711, and numeric data in columns B and C in the same rows. The record numbers on Sheets 2-4 vary, some will have more than one of the same, some will not have all of the record numbers from Sheet 1. I use SUMIF to compare, collect, and condense numeric data for the various records so I can have a total for each record on Sheet 1.

In the wizard for formulas, it describes SUMIF as:

sumIf(if_range, criteria, [sum_range])

Sums cells that meet the specified criteria. If the 'sum_range' range is omitted, cells from the 'if_range' range are used. The 'sum_range' and 'if_range' ranges must have the same number of columns and rows. The criteria can be one of the following:

(1) a number or a text string,

(2) a text string beginning with the =,>,>=,<,<= operators - numeric and date/time searches requires unformatted numbers and generic date/time strings (YYYY-MM-DD),

(3) a search pattern: a text string containing special characters '?' (any character) or '*' (any string, including an empty string). To search for ? or * place a tilde (~) before them.

=sumIf({1,2;3,2}, 2,) returns 4

=sumIf({1,2;3,4}, ">2",) returns 7

=sumIf({"abcde","def";"abc","a"}, "?bc*", {2, 4; 3, 1}) returns 5

=sumIf({"abcde","def";"bc","a"}, "bc", {2, 4; 3, 1}) returns 3

=sumIf({"ABcde","def";"Bc","a"}, ">=bc", {2, 4; 3, 1}) returns 7

The syntax is listed as : =sumIf(if_range, criteria, [sum_range])

I tired to collect it from Sheet 4 column C as follows using both the wizard function under formula and also manually typing in the data:

=sumIf(NM!A2:A20711,Events!A2:A467775,NM!C2:C20711)

I have used the above formula on Open Office Calc and it returns the proper result.

I experimented with inserting the Sheet name before the cell ranges and this made no difference:

=sumIf(NM!A2:NM!A20711,Events!A2:Events!A467775,NM!C2:NM!C20711)

I tried copying & pasting the Sheet 4 data into blank columns (AE, AF, and AG) on Sheet 1 and got the same result:

=sumIf(AE2:AE20711,A2:A467775,AG2:AG20711)

Problem 2: Auto-filling rows with SUMIF calculations

If I can cross this bridge, the next thing I need to do is to have this calculation repeated for rows 3:467775 on Sheet 1. Is there a way to easily do so? On Open Office Calc, I can double-click the "handle" of the cell with the formula and it automatically copies the formula for the remaining rows.

Thank you for any help, I look forward to using the program.