I purchased the program due to needing to work with several million+ row spreadsheets. It loads and saves them very quickly. I’m using Windows 7 Ultimate 64 bit and the 64 bit v. 11.7.6 of GS-CALC.
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.
: