SUMIF function does not appear to work in 11.7.6, copy calcs

User Help for GS-Calc

Re: SUMIF function does not appear to work in 11.7.6, copy c

Postby Crash-14 » Sat Oct 25, 2014 1:09 am

Thank you for the detailed reply. The fact is I only need to do a total of 5 of these files, and they will never be increased as they are historical data. The above looks like a lot of work and the opportunity for more keystroke errors, my typing skills are not the best. I have a good template down for =SUM(SUMIF), and have already completed one file, am halfway through the second, and have a third running on an unused laptop. I will stick with this process for the remainder.

I really appreciate you education on your program, it has been a good investment.
Posts: 18
Joined: Wed Oct 22, 2014 5:58 am

Re: SUMIF function does not appear to work in 11.7.6, copy c

Postby citadel5 » Sun Oct 26, 2014 7:20 pm

This is mostly copying and pasting but if you would like to avoid it, you can simply use the pivotData() function. This will generate the whole record/sums sorted report with just one (array) formula entered in sheet1, for example:

=pivotData(sheet2!D1:F1000000, {1},,{2,3}, {PIVOT::Sum,PIVOT::Sum}, PIVOT::RowGrandTotals + PIVOT::ColumnGrandTotals + PIVOT::SubTotals)

or with two formulas for each column of sums:

=pivotData(sheet2!D1:F1000000, {1},,{2}, {PIVOT::Sum}, PIVOT::RowGrandTotals + PIVOT::ColumnGrandTotals + PIVOT::SubTotals)
=pivotData(sheet2!D1:F1000000, {1},,{3}, {PIVOT::Sum}, PIVOT::RowGrandTotals + PIVOT::ColumnGrandTotals + PIVOT::SubTotals)

(where the D1, E1 and F1 cells contain e.g. the following labels: "record number", "injury", "fatal".)

However the report will contain only those record numbers which occur within the sheet2!D1:D1000000 range. To display all record numbers (e.g. 1...n), you would need to add the series of 1...n numbers below sheet2!D1000000 and
change the above sheet2!D1:F1000000 to include these numbers.

The above will be slower than the previous solution, but still much faster than SUMIFs (for very large data sets).

If you need something like that in the future, please take a look at the corresponding help topics and the sample.gsc file, which contains a few pivot tables.
User avatar
Posts: 120
Joined: Tue Apr 20, 2010 11:58 pm


Return to GS-Calc Support

Who is online

Users browsing this forum: No registered users and 1 guest