Outline ·
[ Standard ] ·
Linear+
Excel question for excel Gurus, How to add numbers based one 3...
|
wlcling
|
 Nov 29 2005, 09:05 AM
|
|
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
|
|
|
|
|
|
wlcling
|
Nov 29 2005, 10:32 AM
|
|
|
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.
|
|
|
|
|
|
wlcling
|
Dec 6 2005, 04:30 PM
|
|
|
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...
|
|
|
|
|
|
wlcling
|
Dec 19 2005, 07:07 PM
|
|
|
QUOTE(kar2on @ Dec 7 2005, 09:22 AM) 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. 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. |
|
|
|
|
|
wlcling
|
Jan 26 2006, 11:11 AM
|
|
|
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
|
|
|
|
|
Quote
0.0147sec
0.31
6 queries
GZIP Disabled