(sum + match + offset = headache)

[Polymorpher]

The attachment should be pretty easy to figure out.

I need to use keyword criteria and sum the numbers 1 cell to the right of where the match/es is/are.

Anyway, don't suggest rearranging the file because the actual file is insanely huge.

[Richard Buttrey]

Hi,

We don't usually like being restricted in the advice we offer. And in this case the best advice I can offer is to rearrange your data.

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.


A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

[Polymorpher]

You are obviously right, however its not always upto you to create the initial file.

The thing i'm asking for i have seen done before, but trying to find it again has been a failure so far.

[XOR LX]

Hi.

Try this in O3:

=SUMPRODUCT(0+(Terrain!$B$7:$AA$33=N3),Terrain!$C$7:$AB$33)

Copy to the other cells as required.

Regards

[Polymorpher]

I really have to get a training course one of these days. It seems to be simple if you know what you are doing.

Thank you both - thread solved.

Why is the "0+" in there btw? What does it do?

[XOR LX]

I really have to get a training course one of these days. It seems to be simple if you know what you are doing.

Thank you both - thread solved.

Why is the "0+" in there btw? What does it do?

You're welcome.

The clause:

(Terrain!$B$7:$AA$33=N3)

will generate an array of Boolean TRUE/FALSE responses as to the question of whether each of the entries in the array Terrain!$B$7:$AA$33 is equal to the value in N3 or not.

Since SUMPRODUCT requires that the values passed to it for summing be numerical, we first coerce these Boolean TRUE/FALSE values into their numerical equivalents (TRUE=1, FALSE=0).

Any suitable mathematical operation which also leaves these values unchanged will suffice for this. I chose to add zero, though multiplication by 1 and double-negation (--) are two other popular - and perfectly reasonable - alternatives.

Regards