Re: Conditional sum matching two columns and a row
- From: "Biff" <biffinpitt@xxxxxxxxxxx>
- Date: Mon, 25 Sep 2006 22:30:54 -0400
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" <L@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4069409A-4138-42CE-8026-C3669AA66B4B@xxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- References:
- Prev by Date: Re: Naming Cell in diffetn worksheet
- Next by Date: Vlookup and few more questions :)
- Previous by thread: Re: Conditional sum matching two columns and a row
- Next by thread: Re: Conditional sum matching two columns and a row
- Index(es):
Relevant Pages
|
