It "finds" (matches) the first TEXT entry after the specified name to define
the range. For example:

A2 = Name1
A3
A4
A5
A6 = Name2

Suppose the name criteria is Name1. We need to know where Name1 ends and
Name2 begins. That's what MATCH("*"...) does. It finds the criteria Name1
then finds the next TEXT entry which would be Name2 then just calculates how
many cells are in between to give us the range for Name1 which would be
A2:A5. We get the other columns using OFFSET.

Biff

"~L" wrote in message
...
After reading the formula, there's only one that that confuses me... What
is
match("*" ...(etc) ) matching?

"Biff" wrote:

OK, here's a sample file:

Sum non contiguous criteria range(1).xls 14.5kb

http://cjoint.com/?jAemVQezto

The sample formulas are based on 3 criteria: Name, code and property.
You'll
notice that I left the code criteria cells empty and just hard coded that
criteria directly into the formulas. We can make that more dynamic by
simply
entering some code in the criteria cells but I don't know what all your
different criteria might be. The formulas calculate on the name, the
property and codes that contain "OT".

The "complicated" version is based on the description of your post using
merged name cells. For that version I added an "end of range" flag.

The "simple" version uses the helper column F where the names are in
every
cell.

There's quite a difference between versions.

Biff

"~L" wrote in message
...
I am still following. I have not yet found a solution in a simple
formula
and have begun breaking it down into a more complicated series of
formulas.

By the way, I figured out that the reason the "*OT" wasn't working in
my
other formulas was because they were array formulas and wildcards are
not
allowed (as of Excel 2000, I don't know if that changes in later
versions).

Unfortunately the program I'm exporting from merges the cells
automatically
where there would be a space in that header column. It would be easy
enough
to create a macro to unmerge all the merged cells and fill the boxes
with
the
information of the box above it, but one of my goals is to not
manipulate
the
export data if possible.



"Biff" wrote:

Are you still following this thread?

This can be done based on your current layout but it is extremely
complicated. On the other hand, this would be extremely simple if you
could
change the layout to include the persons name in every cell of the
header
row. Then, just a basic Sumif would do the job.

Biff