Alternative to SUMIF

[oneyejack77]

How can I create this calculation without SUMIF? Currently I have about 30,000 rows, each row has an associated "Inventoryid" (columnQ). Each "Inventoryid" shows up 3 times (once per fiscal month, 3 months of data). If I use SUMIF my workbook becomes practically unusable due to all the resources needed to recalculate that much data.

Another helper column to get there would be great, I'm data stumped. Thanks for your help!

Here is a snip of what I'm trying to do.

Capture.PNG

[AliGW]

What's the SUMIF formula you are currently using? It's much easier to help if you provide a sample workbook.

[spitfireblue]

Is your data always sorted by inventoryid?

[FDibbins]

No real shortcut to what you, that I can think of.

If your data is already sorted, and wont be added to, you could maybe convert the sumif formulas using copy/paste values

Can you show a sample of your formula?

[JohnTopley]

If you are OK to use VBA: this SUMs every 3 rows i.e. assumes data sorted by Inventory ID

Attached file has 30000 rows.

Please Login or Register  to view this content.

You may need to change column references (currently B for Data, C for Output

[oneyejack77]

The VBA was working great until I discovered about 3000 rows down that I do not have exactly 3 matching inventoryid for every instance. So unfortunately sorting by inventoryid and summing every 3 does not work. It will eventually become misaligned.

Ultimately here is what happened about 3000 rows down

Capture.PNG

SUMIF would work perfect to only sum matching inventoryid if it didn't slow my workbook down.

Here is the SUMIF I tried to use

=SUMIF($Q$3:$Q$30000,Q3,$C$3:$C$30000)

[JohnTopley]

Try this : modified VBA

Please Login or Register  to view this content.

[spitfireblue]

Or you could just try

Please Login or Register  to view this content.

[FDibbins]

@ spitfireblue, OP is trying to avoid using SUMIF because it slows their file down

[spitfireblue]

@FDibbens - true but calculating a SUMIF on 5 rows of data will be a lot quicker than on 30,000 rows of data, especially when you are doing it 30,000 times

[JohnTopley]

Why 5 rows ?????

[FDibbins]

I was wondering that too, John?

[spitfireblue]

@JohnTopley @FDibbins
I believe that each inventoryid shows up a maximum of 3 times and that the sheet is sorted by inventoryid based on the information that we have been given. Therefore my formula in row 3 will pick up the duplicate inventoryids in rows 3-5 (and ignore 1 and 2), then in row 4 will pick up the duplicate inventoryids in rows 3-5 (and ignore 2 and 6) etc.
Maybe I am missing something?!?

[oneyejack77]

Great suggestions from everyone, thanks so much for the help. @Spitfireblue you were right on! I reduced the number of calculations significantly and that cleared up the bottle neck. I used your suggested formula on 3 different columns 30,000 times each and it worked great.

[JohnTopley]

I tried the formula (iF i Have it right) but did not get the right results:

[JohnTopley]

Even starting in row 3 (which I think is what is needed), it only works for consistent blocks of 3.

[spitfireblue]

@JohnTopley Yes it only works for consistent blocks of 3, but that is all that was needed in this particular case. It's not a perfect solution but sometimes the simple solution is all that is needed.

[JohnTopley]

The reason I changed the VBA was because the OP said

I do not have exactly 3 matching inventoryid for every instance.

.

So I cannot see how he states the formula is OK in his particular case; I agree with your logic for a consistent set of 3s- very inventive

[spitfireblue]

Because my logic also works on sets of 2 and for individual ids.
My assumptions based on the information we had been given were that there was a maximum of 3 matching ids and the ids were sorted.