Re: Conditional sum matching two columns and a row

Tech-Archive recommends: Speed Up your PC by fixing your registry

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" <L@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0E65ECFC-E376-4C2C-B3E4-308F9AE89787@xxxxxxxxxxxxxxxx
Please pardon my sloppy formatting for the data. In your example, do the
C3:C7 and D3:D7 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 *** 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 *** 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",D3:D7)

Biff




.

Quantcast