Working with large data sets within Numbers on iPad

I think I get what you're trying to do now...

You have data in cells A3:A109404 that we'll call source_range.

You're then trying to extract the first 7 characters of these cells and put them into B3:B109404

You're then trying to run a SUMIF() against those truncated values, using whatever value is in $F3 as the comparison string, and $C3:$C109404 as the values to sum... correct?

So, first off, unless there's a compelling reason to have column B with the truncated values, you can just use the LEFT() function within the SUMIF().

I see why you're doing it (sometimes it's easier to break out the 7 characters), but there's no inherent need, unless that's useful for some other purpose. SUMIF() will happily calculate the LEFT() component as part of its flow, which is what I showed in my earlier example.

> I was able to update the column manually taking approximately 20 minutes. I was hoping to discover a quicker way.

It sounds like you've achieved what you were aiming for, so I'm not sure what part you are looking to streamline...?

Two things come to mind...

First, if you want to create column B with the first 7 characters of the values in column A, then this formula in cell B2 will do the trick:

=LEFT(A,7)

By entering this in the first non-header row of the column, you're essentially telling it to calculate LEFT() for the entire column A. This will automatically grow and shrink as rows are added to/deleted from the table (it's referencing the entire column, not a specific set of rows).

The same techique can be used for the SUMIF() function... rather that specifying the range of rows to compare, you can just include the entire column:

=SUMIF(B,$F3,C)

and it will compare all values in column B (extending/shrinking to catch additions/deletions) and sum the matching values from column C.

Combining the two measures, if you want to nix column B altogether, you can just:

=SUMIF(LEFT(A,7),$F3,C)

and now it will compare the 7 leftmost characters in column A against the value in $F3, without the need of the intermediary column.

I'm not sure what else you're looking/hoping for here...

Are you a recovering Excel user? If you're used to using tables that large, I hope someone bothered to show you how to move your cursor around with the keyboard. We'll start with the basics.

• Arrow keys on your keyboard move the active cell pointer around. (up, down, left, right).
• If you add the Shift key to that, it starts selecting a range. So click into A1, then hold down Shift, then move to the right three columns and down four rows. You now have twelve cells selected. Shift is Select multiples.

You can use the Command key on Apple or Cntrl on Windoze to jump through ranges quickly.

• Click into the first row of your data. Command and Down Arrow. The cell pointer moves to the end of the range. If there's an empty cell, that's where it stops. So if you have a continuously populated table, you can jump to the bottom very quickly with Command--Down Arrow. Command--Right Arrow does the same except across columns. This is how you get to the end of 100,000 rows quickly. Command is Jump ranges instead of cells
• And now you combine the two skills: Hold down Shift (Select) and Command (jump to end of range) and use your arrow keys. Click in cell A1, Shift--Command--down arrow, then right arrow. Presto! Your whole 100,000 range is selected.

If you need to fill down a formula or multiple formulas, copy them on the first row. Then Shift--command--down arrow where you want them added. Edit-->Paste. Done. I could fill in a row of formulas on 100,000 rows in a couple of minutes.

It isn't quite clear what you're asking for here.. .can you give a little more detail?

Specifically...

> ... I need to copy a function in a column for every row.

Ok.. and you can't do this because...?

Other than the fact that 100,000 is a lot, if it's the same function (or can be expressed as a single function), then entering it once and using Fill Down to fill out the rows should still be viable...

> Alternatively I need to nest the left() function within a sumif() function. I was not successful with the later.

Without knowing your data and what you're trying to do, it's hard to understand this one, but I'll take a stab...

SUMIF() takes three arguments... a source range, a comparison rule, and a values range.

Each value in the source range is compared against the comparison rule. If it matches (the comparison returns TRUE()), then the corresponding value in the values range is added to the result.

For example, if you want the SUMIF() to sum all the values where the source entry begins with "A", you could use:

=SUMIF(LEFT(A,1),"A",B)

This starts by taking all the values in column A and extracting the LEFT()most character. This array becomes your source range. These values are compared to the string "A", and for every match the corresponding value from column B is added.