Just learnt a powerful new Excel technique – Page 3 – Chat Forum

Stoner · 2012-04-29T18:52:05Z

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 [b]AB[/b]123 JJ23452345 [b]AB[/b]CVS AFF908 [b]AB[/b]4 LK09780987 You want to sum corresponding values in a neighbouring column for any code that STARTS "[b]AB[/b]". (lets put "[b]AB[/b]" 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. :oops: Imagine what other functions you could do this with (LEFT, RIGHT, MATCH FIND etc!) Sorry. But Ive earned my bottle of wine now.

RobHilton

Posts: 5346

Free Member

Let the sediment build up sediment and you could be looking at a NOT 🙂

God knows how but I've passed my level 1 and I haven't even touched any LCS yet! Some disorganisation going on at my college...

Posted : 02/05/2012 8:54 am

Stoner

Posts: 36

Free Member

Topic starter

I haven't even touched any LCS

I wouldnt be too worried. 'orrible stuff. We have to cut our own threads by hand - no automatic dieing machines in our college! Doing an assessment piece at the moment - hanging two rads - with two step overs. Its unforgiving stuff.

have tried lots of workarounds and just cant get offset and indirect to work together in this formula. Im going to have to do direct formula 🙁

Posted : 02/05/2012 9:26 am

RobHilton

Posts: 5346

Free Member

It's defnly do-able, but I'm a bit too rusty at this stuff now to give an answer without wasting lots of time

Posted : 02/05/2012 9:39 am

Stoner

Posts: 36

Free Member

Topic starter

ach. Its done w directs now. Deadline looming in 48 hrs - sometimes you just have to crack on and damn the finesse.

If I find a workaround once the model's done Ill drop it in later.

Posted : 02/05/2012 9:53 am

Forum menu

[Closed] Just learnt a powerful new Excel technique