I want to sum numbers in a paticular column based on 3 other columns. So SUMIF is out of the question because it can only handle one. I tried using a PIVOT, but i don't like the loading time, and the constant need to refresh. The data is being pulled of a database, and it has something like 18,000 rows of data. Does anyone here have a solution, I don't want to do DSUM because it's pretty complicated. I basically want to pivot, without a pivot. Sort of like a SUMIF based on 3 columns.

Here's what I need:-

SUM all numbers in COLUMN X When column Y is "home" and column J is "speaker" and column Z is "sound".

Then I need to repeat it for ALL POSSIBLE COMBINATIONS, column Y has about 60 different combinations, J about 40 and Z about 12. Each Change in Z is stored in a new sheet .

I was thinking of making a macro to create a chart only when neeeded. BUt this is also pretty complicated. Anyone have any ideas.??

Err why not let each of them have their own sum

From there set the condition
IF (X is home AND Y is speaker AND z is sound) then take the sum of the three to be displayed

sounds complicated??? not really

kar2on,


Since you are pulling the data out from the database. You can create a view/query to calculate that. Later use the EXCEL to generate the PIVOT out from it. You will like the performance of this approach.


Happy Coding

After a couple hours of fidelling, and about 1 1/2 hours of trying to figure a better way to use DSUM , i pretty much stuck back with Pivots, though they don't look nice, they're the best performance wise. Here's something else, I seem to have some odd behaviour with the pivot, sometimes certain data goes missing, it seems to filter it out. Anyone know why? Is it because I base the pivot on another pivot? Will that work?? Besides pivot, anyone know of a creative way to sum a column based on 3 different other columns. I still haven't given up on DSUm, but i can't figure a practical way to do it. THere are about 125*8*12 different combos. Would appreciate your help. Thanx.

kar2on,


I have not try doing PIVOT on another PIVOT. Theorically, it should be work and will create some complexity on your design. PIVOT is so dynamic and the user allow to change the column and row at any point of time. You final PIVOT must small enought to filter which row and column to select. I am watching a twisted PIVOT tables, can you?

Back to my initial suggestion, try use the database's query/view for your data massage process. Is extra step but it will reduces the complexity on your design.

You are the one make the call. Hence, you are the one develop/maintain it.


Happy PIVOT-ing

hmmm....... I dunno anything about databases, luckily we use brio here. Can't get the data to port any other way.... I don't want to base the pivot on external data because I will need to zoom down to the full data later, but a filtered version.

I stick with array formulas in cases like this...

Using a crude example, lets say:-

from A1 to A10, you have header "AMOUNT" - which will contain numbers of course
from B1 to B10, you have header "PLACE" - which will contain values such as "home", "office", etc...
from C1 to C10, you have header "ITEM" - such as "speakers", "television"
from D1 to D10, you have header "ITEM FUNCTION" - such as "sound", "picture"

So now, you want to sum up those numbers in Column A, which will fit your criterias in Column B till D. Correct?

Key in this into an empty cell.
=SUM((B2:B10="home")*(C2:C10="speakers")*(D2:D10="sound")*A2:A10)
followed by a "CTRL-SHIFT-ENTER" which will then make the formula look like
{=SUM((B2:B10="home")*(C2:C10="speakers")*(D2:D10="sound")*A2:A10)}

Note the extra brackets outside once you key in Ctrl-shift-enter? You must do it this way, not manually keying in the brackets.

Not dynamic enough? Instead of direct hardcoding the formula to look for definite values like above, you can also link to cells instead.

Hope this helps

Sorry, just noticed your huge amounts of data...

Please ignore my array formula posted earlier. They're terrible performance-wise although it eliminates the need of a criteria field unlike DSUM, etc...

I'm personally facing this problem myself now, around 10000 rows by 10 or so columns in which performance is really bad especially when I have many linked array formulas to the database list.

Hahaha...try doing with around 8000 rows * 25 columns * 2 Sheets of data. Die standing. I prefered the pivot way, though with VBA ,pivots proved a tad bit unstable they're MUCH MUCH better than the DSUMS. Pivots are pretty awesome, in this respect I would think there is NO other feasible way.. I even tried working on a "real-time" filter which had VBA create the DSUM variables for me based on user input but that proved unsuccessful and hugely problematic. So i guess in conclusion...use pivots.

*Note when using VBA it's possible to change the pivot page table entry to a value that doesn't exist. Once this is done, (i've discovered) there's almost no turning back. You've probably screwed up the entire pivot and be prepared to have top redo the entire pivot again.

*pivots are also irratating when it comes to formatting. You have do some enable selection -> preserve formatting....etc, pretty annoying.

true true... i don't really touch pivots because for one thing:- I haven't touched it before, and so far I have worked around ways not requiring the use for pivots. One more reason is the formatting...

i find it suprising that for a piece of software worth roughly RM450 (just my guess) on its own, Excel can't do something this trivial. I would think that referencing 2 or 3 rows is pretty common . Anyway I would like to share one extra way that's a bit redundant and less "canggih" but overall remarkably easier.

If you want to reference 2 columns, what you need to do is create a separate column that references those 2 columnjs. Then you SUMIF based on your new row. So Create a column that concatenate the strings like "homespeakersound" and then sumif if your new column in "homespeakersound" . Though you introduce a new (redundant) column, I personally feel this added filesize and slight performance drop (compared to pivots) is quite worthwhile considering the simplicity of the approach.

This post has been edited by kar2on: Dec 7 2005, 09:25 AM

I like this approach. Its like those kind of moments in excel where you tell yourself "Why have I not thought of this before"

But its actually not a performance drop is it? After all, a change in the data will only cause the formula to look through the new column rather than the previous 2 old columns.

Just wanted to update this, actually I found another way to do it. Apparently it came out in tech tv. There is actually a "conditional sum" wizard in Excel. It can create a sum based on many many conditions, but taked ages to load up (on a p3 processing 7000*28 worksheet). Sometimes excel doesn't have it loaded by default, you need to install it (you may need your office cd). I couldn't use this approach, since using now means rewriting lots of my code. Plus the added problem, I can't figure out how to write the code into VBA.

The conditional sum works somewhat like my array formula posted earlier. Performance wise, I think it will fare no better, so I guess there should be a better solution out there... Until then... we wait for the next line of processors.. lol