Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will a Nested IF/SUMIF function instead of VBA?
Hello again Excel Wizards!
Last year, I worked pretty hard on creating VBA to split my data and organize it so that it was 1) uniform and 2) organized for the process of picking at it just to get the sum of numbers in one column... So now I'm wondering if that was overkill and if it could be done with an IF function, but all I keep thinking about is that it would be too long to type into a cell. Sorry for the length, but here's what I'm going for: Currently, I'm extracting data from a third-party program into Excel. The columns, however are not always in the same place which is why I had chosen to write a VBA macro to extract those select few columns and place them into the same order on another sheet for the purpose of searching through it and summing up the data if it meets my criteria. Here's a sample of what it (the seperate file) looks like (I have used commas to seperate fields as spacing could be a problem on the screen): EMPLOYEE,HEAD_COUNT,LOCATION_CODE,DIVISION_CODE,ST ATUS,etc... Ronny,1.0,99990001,99,Temp,... Jennifer,1.0,98990001,98,Temp,... Roger,1.0,99990001,99,Perm,... Here's what I'd like to do in another Excel file: I want an IF function (SUM IF maybe, but not VBA/Macro code) that will first check the STATUS column (in the data file) for Temp or Perm, and if it's Perm I would like it to tally up the numbers in the HEAD_COUNT field if the first four numbers in the LOCATION_CODE matches my criteria. This would basically give me a total number (I have to use a SUM of the HEAD_COUNT because it could be a 0.5) for a location (signified by the first two digits of the LOCATION_CODE) if and only if they are a Perm employee. The macro (that I'm using now) splits the data by Location, removed all Temp employee records, and sums up the HEAD_COUNT for each location into a subtotal or sorts. It also names/defines each subtotal with the name of "LocationXXXX" where "XXXX" is the 4 digit location number. Then in another document (my totals document), I have calls to the already split-up/organized files for the subtotal numbers using the named/defined fields. I hope this all makes sense! As I said, if there's no way to perform all of these things within an IF or SUMIF function, it's no big deal since I have a working Macro. Thank you! Ronny |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will a Nested IF/SUMIF function instead of VBA?
Hello.
I am not quite sure if I understood the last part correct, but if you only need a formula to sum head_count this one could be used: =SUM((E2:E4="Perm")*(LEFT(C2:C4;4)="9999")) Replace "9999" with the cells that hold your criteria. It is an array formula so you have to press Ctrl+Shift+enter when you edit it. I presumed that Status is in column E, location in C and head_count in B Per Erik On Tue, 3 Jan 2006 12:16:02 -0800, Ronny Hamida wrote: Hello again Excel Wizards! Last year, I worked pretty hard on creating VBA to split my data and organize it so that it was 1) uniform and 2) organized for the process of picking at it just to get the sum of numbers in one column... So now I'm wondering if that was overkill and if it could be done with an IF function, but all I keep thinking about is that it would be too long to type into a cell. Sorry for the length, but here's what I'm going for: Currently, I'm extracting data from a third-party program into Excel. The columns, however are not always in the same place which is why I had chosen to write a VBA macro to extract those select few columns and place them into the same order on another sheet for the purpose of searching through it and summing up the data if it meets my criteria. Here's a sample of what it (the seperate file) looks like (I have used commas to seperate fields as spacing could be a problem on the screen): EMPLOYEE,HEAD_COUNT,LOCATION_CODE,DIVISION_CODE,S TATUS,etc... Ronny,1.0,99990001,99,Temp,... Jennifer,1.0,98990001,98,Temp,... Roger,1.0,99990001,99,Perm,... Here's what I'd like to do in another Excel file: I want an IF function (SUM IF maybe, but not VBA/Macro code) that will first check the STATUS column (in the data file) for Temp or Perm, and if it's Perm I would like it to tally up the numbers in the HEAD_COUNT field if the first four numbers in the LOCATION_CODE matches my criteria. This would basically give me a total number (I have to use a SUM of the HEAD_COUNT because it could be a 0.5) for a location (signified by the first two digits of the LOCATION_CODE) if and only if they are a Perm employee. The macro (that I'm using now) splits the data by Location, removed all Temp employee records, and sums up the HEAD_COUNT for each location into a subtotal or sorts. It also names/defines each subtotal with the name of "LocationXXXX" where "XXXX" is the 4 digit location number. Then in another document (my totals document), I have calls to the already split-up/organized files for the subtotal numbers using the named/defined fields. I hope this all makes sense! As I said, if there's no way to perform all of these things within an IF or SUMIF function, it's no big deal since I have a working Macro. Thank you! Ronny |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will a Nested IF/SUMIF function instead of VBA?
Hi
With table on sheet Data (row 1 are headers), on fly: =SUMPRODUCT(--(Status="Perm"),--(LEFT(Location,2)*1=Criteria),Head) where Status - a named range defined as INDEX(DataTable,,MATCH("STATUS",DataHead,0)) Location - a named range defined as INDEX(DataTable,,MATCH("LOCATION_CODE",DataHead,0) ) Head - a named range defined as INDEX(DataTable,,MATCH("HEAD_COUNT",DataHead,0)) , where DataTable - a named range defined as =OFFSET(Data!$A$1,1,,COUNTA(Data!$A:$A)-1,COUNTA(DataHead)) DataHead - a named range defined as =OFFSET(Data!$A$1,,,1,COUNTA(Data!$1:$1)-1) Arvi Laanemets "Ronny Hamida" wrote in message ... Hello again Excel Wizards! Last year, I worked pretty hard on creating VBA to split my data and organize it so that it was 1) uniform and 2) organized for the process of picking at it just to get the sum of numbers in one column... So now I'm wondering if that was overkill and if it could be done with an IF function, but all I keep thinking about is that it would be too long to type into a cell. Sorry for the length, but here's what I'm going for: Currently, I'm extracting data from a third-party program into Excel. The columns, however are not always in the same place which is why I had chosen to write a VBA macro to extract those select few columns and place them into the same order on another sheet for the purpose of searching through it and summing up the data if it meets my criteria. Here's a sample of what it (the seperate file) looks like (I have used commas to seperate fields as spacing could be a problem on the screen): EMPLOYEE,HEAD_COUNT,LOCATION_CODE,DIVISION_CODE,ST ATUS,etc... Ronny,1.0,99990001,99,Temp,... Jennifer,1.0,98990001,98,Temp,... Roger,1.0,99990001,99,Perm,... Here's what I'd like to do in another Excel file: I want an IF function (SUM IF maybe, but not VBA/Macro code) that will first check the STATUS column (in the data file) for Temp or Perm, and if it's Perm I would like it to tally up the numbers in the HEAD_COUNT field if the first four numbers in the LOCATION_CODE matches my criteria. This would basically give me a total number (I have to use a SUM of the HEAD_COUNT because it could be a 0.5) for a location (signified by the first two digits of the LOCATION_CODE) if and only if they are a Perm employee. The macro (that I'm using now) splits the data by Location, removed all Temp employee records, and sums up the HEAD_COUNT for each location into a subtotal or sorts. It also names/defines each subtotal with the name of "LocationXXXX" where "XXXX" is the 4 digit location number. Then in another document (my totals document), I have calls to the already split-up/organized files for the subtotal numbers using the named/defined fields. I hope this all makes sense! As I said, if there's no way to perform all of these things within an IF or SUMIF function, it's no big deal since I have a working Macro. Thank you! Ronny |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will a Nested IF/SUMIF function instead of VBA?
you should be able to use something like this
=SUMPRODUCT(--(E2:E4="Perm"),--(LEFT(C2:C4,4)="9999"),B2:B4) You will need to use the SUMPRODUCT function because you have more than one qualifier (SUMIF can only have one). The first two are the qualifiers and produce the following arrays. {0,0,1} {1,0,1} The last one is simply the headcount as an array and produces this array {1,1,1} The result are each item in the arrays mulitplied together and summed (ie 0*1*1+0*0*1+1*1*1). You should be able to figure something out from this. Also, you didn't say what your criteria was for the location code, so I made one up. "Ronny Hamida" wrote: Hello again Excel Wizards! Last year, I worked pretty hard on creating VBA to split my data and organize it so that it was 1) uniform and 2) organized for the process of picking at it just to get the sum of numbers in one column... So now I'm wondering if that was overkill and if it could be done with an IF function, but all I keep thinking about is that it would be too long to type into a cell. Sorry for the length, but here's what I'm going for: Currently, I'm extracting data from a third-party program into Excel. The columns, however are not always in the same place which is why I had chosen to write a VBA macro to extract those select few columns and place them into the same order on another sheet for the purpose of searching through it and summing up the data if it meets my criteria. Here's a sample of what it (the seperate file) looks like (I have used commas to seperate fields as spacing could be a problem on the screen): EMPLOYEE,HEAD_COUNT,LOCATION_CODE,DIVISION_CODE,ST ATUS,etc... Ronny,1.0,99990001,99,Temp,... Jennifer,1.0,98990001,98,Temp,... Roger,1.0,99990001,99,Perm,... Here's what I'd like to do in another Excel file: I want an IF function (SUM IF maybe, but not VBA/Macro code) that will first check the STATUS column (in the data file) for Temp or Perm, and if it's Perm I would like it to tally up the numbers in the HEAD_COUNT field if the first four numbers in the LOCATION_CODE matches my criteria. This would basically give me a total number (I have to use a SUM of the HEAD_COUNT because it could be a 0.5) for a location (signified by the first two digits of the LOCATION_CODE) if and only if they are a Perm employee. The macro (that I'm using now) splits the data by Location, removed all Temp employee records, and sums up the HEAD_COUNT for each location into a subtotal or sorts. It also names/defines each subtotal with the name of "LocationXXXX" where "XXXX" is the 4 digit location number. Then in another document (my totals document), I have calls to the already split-up/organized files for the subtotal numbers using the named/defined fields. I hope this all makes sense! As I said, if there's no way to perform all of these things within an IF or SUMIF function, it's no big deal since I have a working Macro. Thank you! Ronny |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will a Nested IF/SUMIF function instead of VBA?
On Tue, 3 Jan 2006 12:16:02 -0800, Ronny Hamida
wrote: Hello again Excel Wizards! Last year, I worked pretty hard on creating VBA to split my data and organize it so that it was 1) uniform and 2) organized for the process of picking at it just to get the sum of numbers in one column... So now I'm wondering if that was overkill and if it could be done with an IF function, but all I keep thinking about is that it would be too long to type into a cell. Sorry for the length, but here's what I'm going for: Currently, I'm extracting data from a third-party program into Excel. The columns, however are not always in the same place which is why I had chosen to write a VBA macro to extract those select few columns and place them into the same order on another sheet for the purpose of searching through it and summing up the data if it meets my criteria. Here's a sample of what it (the seperate file) looks like (I have used commas to seperate fields as spacing could be a problem on the screen): EMPLOYEE,HEAD_COUNT,LOCATION_CODE,DIVISION_CODE,S TATUS,etc... Ronny,1.0,99990001,99,Temp,... Jennifer,1.0,98990001,98,Temp,... Roger,1.0,99990001,99,Perm,... Here's what I'd like to do in another Excel file: I want an IF function (SUM IF maybe, but not VBA/Macro code) that will first check the STATUS column (in the data file) for Temp or Perm, and if it's Perm I would like it to tally up the numbers in the HEAD_COUNT field if the first four numbers in the LOCATION_CODE matches my criteria. This would basically give me a total number (I have to use a SUM of the HEAD_COUNT because it could be a 0.5) for a location (signified by the first two digits of the LOCATION_CODE) if and only if they are a Perm employee. The macro (that I'm using now) splits the data by Location, removed all Temp employee records, and sums up the HEAD_COUNT for each location into a subtotal or sorts. It also names/defines each subtotal with the name of "LocationXXXX" where "XXXX" is the 4 digit location number. Then in another document (my totals document), I have calls to the already split-up/organized files for the subtotal numbers using the named/defined fields. I hope this all makes sense! As I said, if there's no way to perform all of these things within an IF or SUMIF function, it's no big deal since I have a working Macro. Thank you! Ronny It sounds like SUMPRODUCT might do what you describe. e.g. something like: =SUMPRODUCT((STATUS="Perm")*(--LEFT(LOCATION_CODE,4)=9999)*HEAD_COUNT) Note that the LEFT worksheet function returns a string; the preceding double-unary converts that value into a number. That should be more user friendly if you replace 9999 with a cell reference. --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will a Nested IF/SUMIF function instead of VBA?
Thank you to all for the assistance!
Erik - The problem with that formula is that the columns are never always in the same position. This formula would be perfect except for that reason alone. :( "Per Erik Midtrxd" wrote: Hello. I am not quite sure if I understood the last part correct, but if you only need a formula to sum head_count this one could be used: =SUM((E2:E4="Perm")*(LEFT(C2:C4;4)="9999")) Replace "9999" with the cells that hold your criteria. It is an array formula so you have to press Ctrl+Shift+enter when you edit it. I presumed that Status is in column E, location in C and head_count in B Per Erik On Tue, 3 Jan 2006 12:16:02 -0800, Ronny Hamida wrote: Hello again Excel Wizards! Last year, I worked pretty hard on creating VBA to split my data and organize it so that it was 1) uniform and 2) organized for the process of picking at it just to get the sum of numbers in one column... So now I'm wondering if that was overkill and if it could be done with an IF function, but all I keep thinking about is that it would be too long to type into a cell. Sorry for the length, but here's what I'm going for: Currently, I'm extracting data from a third-party program into Excel. The columns, however are not always in the same place which is why I had chosen to write a VBA macro to extract those select few columns and place them into the same order on another sheet for the purpose of searching through it and summing up the data if it meets my criteria. Here's a sample of what it (the seperate file) looks like (I have used commas to seperate fields as spacing could be a problem on the screen): EMPLOYEE,HEAD_COUNT,LOCATION_CODE,DIVISION_CODE,S TATUS,etc... Ronny,1.0,99990001,99,Temp,... Jennifer,1.0,98990001,98,Temp,... Roger,1.0,99990001,99,Perm,... Here's what I'd like to do in another Excel file: I want an IF function (SUM IF maybe, but not VBA/Macro code) that will first check the STATUS column (in the data file) for Temp or Perm, and if it's Perm I would like it to tally up the numbers in the HEAD_COUNT field if the first four numbers in the LOCATION_CODE matches my criteria. This would basically give me a total number (I have to use a SUM of the HEAD_COUNT because it could be a 0.5) for a location (signified by the first two digits of the LOCATION_CODE) if and only if they are a Perm employee. The macro (that I'm using now) splits the data by Location, removed all Temp employee records, and sums up the HEAD_COUNT for each location into a subtotal or sorts. It also names/defines each subtotal with the name of "LocationXXXX" where "XXXX" is the 4 digit location number. Then in another document (my totals document), I have calls to the already split-up/organized files for the subtotal numbers using the named/defined fields. I hope this all makes sense! As I said, if there's no way to perform all of these things within an IF or SUMIF function, it's no big deal since I have a working Macro. Thank you! Ronny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested IF with OR Function | Excel Worksheet Functions | |||
Nested "If" Function | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Countif Function -Nested | Excel Discussion (Misc queries) | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |