If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional sum matching two columns and a row
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? |
#2
|
|||
|
|||
Conditional sum matching two columns and a row
This works for me:
=SUMIF(C3:C7,"*OT",D37) Biff "~L" wrote in message ... 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? |
#3
|
|||
|
|||
Conditional sum matching two columns and a row
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 |
#4
|
|||
|
|||
Conditional sum matching two columns and a row
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" wrote in message ... 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 |
#5
|
|||
|
|||
Conditional sum matching two columns and a row
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 |
#6
|
|||
|
|||
Conditional sum matching two columns and a row
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" wrote in message ... 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? |
#7
|
|||
|
|||
Conditional sum matching two columns and a row
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 |
#8
|
|||
|
|||
Conditional sum matching two columns and a row
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" 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 |
#9
|
|||
|
|||
Conditional sum matching two columns and a row
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 |
#10
|
|||
|
|||
Conditional sum matching two columns and a row
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 |
Thread Tools | |
Display Modes | |
|
|