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?

This works for me:

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

Biff

"~L" <(E-Mail Removed)> wrote in message
news:38092E87-5E5F-4DBF-B66C-(E-Mail Removed)...
>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?

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" wrote:

> This works for me:
>
> =SUMIF(C3:C7,"*OT",D37)
>
> 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

"~L" <(E-Mail Removed)> wrote in message
news:0E65ECFC-E376-4C2C-B3E4-(E-Mail Removed)...
> 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" wrote:
>
>> This works for me:
>>
>> =SUMIF(C3:C7,"*OT",D37)
>>
>> Biff
>>
>

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

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

"~L" <(E-Mail Removed)> wrote in message
news:38092E87-5E5F-4DBF-B66C-(E-Mail Removed)...
>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?

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" <(E-Mail Removed)> wrote in message
news:4069409A-4138-42CE-8026-(E-Mail Removed)...
>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
>

Thanks, these were both very helpful!

"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" <(E-Mail Removed)> wrote in message
> news:4069409A-4138-42CE-8026-(E-Mail Removed)...
> >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
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
> news:4069409A-4138-42CE-8026-(E-Mail Removed)...
> >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
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
news:524CB904-E04B-477A-86A1-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:4069409A-4138-42CE-8026-(E-Mail Removed)...
>> >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
>> >
>>
>>
>>