Just learnt a powerful new Excel technique

I know it’s geeky, but these are the pleasures of a freelancer working on a Sunday evening. And I reckon there might be one or two Excel-ent people out there who might like this:

Criteria summing can be tricky. The base forumla rarely do exactly want you want them to. But this is a handy way of applying OR criteria to a SUM.

Say you have a range of codes and you want to sum corrsponding values in a neighbouring range where the codes have particular similarity.

For example, in a range of codes:

DG345345
JUH234234234
SDFDSL12391
SDFSDF1243114
AB123
JJ23452345
ABCVS
AFF908
AB4
LK09780987

You want to sum corresponding values in a neighbouring column for any code that STARTS “AB“. (lets put “AB” in cell F1)

if you use:

=SUMPRODUCT(–(LEFT(A1:A10,LEN(F1))=F1),B1:B10)

what it does is turn A1:A10 into an array of values with the same length as the value in F1 (i.e. two digits) and then compares those two digit array values (“DG”, “JU”, “SD” etc), with the contents of F1 (“AB”) to give an array of TRUEs and FALSEs. The “–” then converts the Trues and Falses into an array of 1s and 0s with which to multiply your Value array with to get the SUMPRODUCT

Bloody fantastic!
I did a little sexy frot. 😳

Imagine what other functions you could do this with (LEFT, RIGHT, MATCH FIND etc!)

Sorry.
But Ive earned my bottle of wine now.

That’s really rather clever; I’ve not seen the “–” bit before. Well done.

Good work Stoner… I may well pinch bits of that. Ta.

Very neat – but for the original problem, wouldn’t SUMIF(…) do what you wanted?

I cant take much credit for it. I had to reverse something posted here
http://www.excelbanter.com/showthread.php?p=384506#post384506

and found I could use the principal in other ways.

Wow …

SUMIF wont handle “LEFT” in the Range argument.

bit of an excel novice, but wouldn’t vlookup do the same thing?

Oh, the nasty world of VBA.

Nice bit of stuff there. There was a VBA role going in Andover @ £650 / day recently…

Bit niche for me, though!

BTW – Im using to group cashflows for 300 rows of data on a dozen sheets that have nicely structured id codes that embed about 4 classifications in the code (i.e. building number, phase number, building floor and sub-plot for example).

This formula lets me make a summary page driven by a handful of drop down boxes that specify the classifications I want to filter by and then aggregates by them or even a combination of filters.

I hate VBA. I really only ever work in functions (which is what that ^ up there is) unless I need to run a macro.

<backs out of thread>

I used to spend hours trying to figure out how to create the right formula only for my mate to create one within 15 mins … 😡

SUMIF wont handle “LEFT” in the Range argument.

Hadn’t realised that – nice one and I’ll tuck that away for when it might be useful!

You could create another column with the LEFT bit in it and still use SUMIF – but it’s a nice trick to do it in the one cell.

but wouldn’t vlookup do the same thing

short answer, no.

You could create another column with the LEFT bit in it and still use SUMIF

Unfortunately I dont “Own” the primary model. Im building a “translator” that overlays. I cant disturb the model itself by including columns, and whilst I could add loads of sheets to do the same thing (and that was going to be my back up) I really wanted to find an efficient single cell solution. And did.

I feel all fuzzy now.

Is confused 😳

The most powerfull excel tool i know is that if you click the bottom right corner of a box and dragg it down it’ll copy the data from that cell down. Winner winner chicken dinner 😀

but wouldn’t vlookup do the same thing
short answer, no.

That’ll do me 😀

=vlookup(“Stwer Pisses on Stoner’s chips”,DataRangeAllposts,1,0)
=#N/A

=ISERROR(Stoner is a God)
=FALSE

*Leaves thread once he realises it wasn’t about colour coding a contact list*

I have a few ‘Stoner nuggets’ stashed away in notepad at work. This is another beauty to add to the list. 😀

Very neat bit of coding.

VBA’s great until you have to check it. Much easier to work out what some whizz kid has done wrong if they stick to conventional functions

Stoner nuggets

😯

can I have them back? 😉

Now that’s a good day’s work, especially after just winning the Spanish motogp!

should’nt it be

=SUMPRODUCT(–(LEFT(A1:A10,LEN($F$1))=$F$1),B1:B10)

?

well duh. 😉
Just indicative code without cluttering it with locks.

neat slick solutions are always gratifying, but it’s hard to get excited by excel 😉

erm………….what tyres would I need for this…..? 😳

Nice. Will have to remember that one and have a play when I get back to the office.

but it’s hard to get excited by excel

Mine certainly gets hard when Im excited by Excel 😉

* reports thread for being the sort of shameless filth that no one should stumble into on a Sunday evening *

That’s a great piece of work.

I’ll be using that. And checking your other threads.

*notes those posting for when he is in power*

Klunk – Member
neat slick solutions are always gratifying, but it’s hard to get excited by excel

Agreed

{Pretends he didn’t dance round the kitchen last week after finally getting a graph to display properly}

Stoner, a fellow excexual.

I have a lot of…deep…stuff.

Hmmmm. I’m picking up extremely high levels of geek

cynic-al – Member
*notes those posting for when he is in power*

*adds cynic-al to the big shared table of individuals that The Spreadsheet Illuminati will keep from “power”*

geek is sexy