Conditional sum matching two columns and a row

[Guest]

I have a program that exports data to excel arranged like the following:
Property 1 Property 2
Property 3
Person1 Code1 10 (Hours)
Code2
Code2_OT
Code3_OT
Person2 Code2
40
Code2_OT 5
Person 3 Code 4 8
Code3_OT
Code4_OT
....n

The cells in the A column are merged. The B column has a useless text label
in it that is the same for all cells. The codes in the C column are somewhat
random, but any overtime code will have _OT after it.

What I'm trying to do is sum the overtime hours codes on one page and
regular hours codes on another page.

I've tried a few different approaches including sumifs and sumproducts and
index with matching. A problem common to all of them so far is that the
formulas return an error when using the wildcard character * to match text
(I've tried *"_OT", "*_OT", and *_OT and all are equally invalid).

Can anyone recommend a formula or approach to this problem?

 

[Biff]

This works for me:

=SUMIF(C3:C7,"*OT",D37)

Biff

 

[Guest]

Please pardon my sloppy formatting for the data. In your example, do the
C3:C7 and D37 refer to ranges for the codes and the data under property 1
for the merged range of person 1?

The formula did return the conditional data instead of an error! Thanks!

Another feature of this data is that the shape will not be the same when
exported a second time. Any person may gain or lose codes and properties may
be included or excluded based on criteria. So I need to add matching by
person and property into this formula.

The second sheet I referred to has a list of names going down column A
across the rows starting at 2, and properties across row 1 from column B on.
The formula is in these cells attempting to pull the consolidated number of
OT hours from the sheet of raw data.

A sumif could work, but the best way I can think of to get the range for the
data to sum would be from the range of the merged cells that contain the
person's name. There doesn't seem to be a way to return that range with a
formula. I also thought to add another conditional that refers from the code
to two colums back where the names are since the relative positions of the
names and codes should stay the same. This still leaves the property to be
matched. Another conditional?

That was when I heard of sumproduct, which I haven't used before. But
sumproduct requires the range to be the same, so data in columns and data in
rows can't be compared (I saw an article that mentioned using transpose in a
sumproduct to turn a row into a column but haven't been able to make that
work yet).

Thanks again for showing me how the wildcard is supposed to work! (Still
can't figure why it wouldn't in my other formulas).

 

[Biff]

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

 

[Guest]

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]

I'll put together a sample file and post a link to it this evening. I'll
demonstrate the complicated method and the simple method.

Biff

 

[Biff]

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

 

[Guest]

Thanks, these were both very helpful!

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

 

[Guest]

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

 

[Biff]

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