Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Summing data
I am using Excel 2000 for work complete tracking. Each row represents construction project. I have sets of columns as follows ITEM = the pay item number, revenue and cost associated QUANTITY = the amount of the ITEM to be billed INVOICE = period (month) in which to bill the QUANTITY CONTRACTOR = company responsible for the work The above set of columns is repeated many times, as there are many items to be billed for each project (row). I need to be able to sum the Quantity per ITEM, INVOICE, and CONTRACTOR. Currently, I have the following formula in a different sheet to sum each item, per 'set', then I sum the sets. The formula is repeated for each item (rows) and each 'set' (columns), which makes for a whole lot of formulas and a large file. This works, but makes the worksheet slow. =SUM(IF(invoice=$B$1,IF(item=$A14,IF(cont=$B$2,qty ,0),0),0)) I tried to be concise, but please let me know if this is too confusing. Is there a cleaner way to do this? Thanks! -- yesac142 ------------------------------------------------------------------------ yesac142's Profile: http://www.excelforum.com/member.php...o&userid=24391 View this thread: http://www.excelforum.com/showthread...hreadid=379938 |
#2
|
|||
|
|||
I think this will be faster
= sumproduct(--(invoicerange=$B$1),--(itemrange=$A14),--(contrange=$B$2),qtyrange) Note with sumproduct you cant use entire columns (A1:A1000 is OK A:A is not) and the arrays in seach segement must be the same size "yesac142" wrote: I am using Excel 2000 for work complete tracking. Each row represents construction project. I have sets of columns as follows ITEM = the pay item number, revenue and cost associated QUANTITY = the amount of the ITEM to be billed INVOICE = period (month) in which to bill the QUANTITY CONTRACTOR = company responsible for the work The above set of columns is repeated many times, as there are many items to be billed for each project (row). I need to be able to sum the Quantity per ITEM, INVOICE, and CONTRACTOR. Currently, I have the following formula in a different sheet to sum each item, per 'set', then I sum the sets. The formula is repeated for each item (rows) and each 'set' (columns), which makes for a whole lot of formulas and a large file. This works, but makes the worksheet slow. =SUM(IF(invoice=$B$1,IF(item=$A14,IF(cont=$B$2,qty ,0),0),0)) I tried to be concise, but please let me know if this is too confusing. Is there a cleaner way to do this? Thanks! -- yesac142 ------------------------------------------------------------------------ yesac142's Profile: http://www.excelforum.com/member.php...o&userid=24391 View this thread: http://www.excelforum.com/showthread...hreadid=379938 |
#3
|
|||
|
|||
I get the #VALUE! error when I tried the SUMPRODUCT function. I worked for summing one set of data, but when I try to combine sets it doesn't. =SUMPRODUCT(--(Input_WC!T3:T28,Input_WC!X3:X28=$B$1),--(Input_WC!R3:R28,Input_WC!V3:V28=$A14),--(Input_WC!U3:U28,Input_WC!Y3:Y28=$B$2),Input_WC!S3 :S28,Input_WC!W3:W28) For clarity (attempt): =SUMPRODUCT(--(invoicecolumn1,invoicecolumn2=$B$1),--(itemcolumn1,itemcolumn2=$A14),--(contractorcolumn1,contractorcolumn2=$B$2),qtycolu mn1,qtycolumn2) Am I doing this wrong? Thanks so far, and anymore ideas would be appreciated! -- yesac142 ------------------------------------------------------------------------ yesac142's Profile: http://www.excelforum.com/member.php...o&userid=24391 View this thread: http://www.excelforum.com/showthread...hreadid=379938 |
#4
|
|||
|
|||
I dont believe you can have the (range,range) format
you will probably have to use =SUMPRODUCT(--(Input_WC!T3:T28=$B$1),--(Input_WC!R3:R28=$A14),--(Input_WC!U3:U28=$B$2),Input_WC!S3:S28)+SUMPRODUCT (--(Input_WC!X3:X28=$B$1),--(Input_WC!V3:V28=$A14),--(,Input_WC!Y3:Y28=$B$2),Input_WC!W3:W28) or =SUMPRODUCT(--(and(Input_WC!T3:X28=$B$1,or(column(Input_WC!T3:X2 8)=20,column(Input_WC!T3:X28)=25))),--(Input_WC!R3:V28=$A14),--(Input_WC!U3:Y28=$B$2),Input_WC!S3:W28) "yesac142" wrote: I get the #VALUE! error when I tried the SUMPRODUCT function. I worked for summing one set of data, but when I try to combine sets it doesn't. =SUMPRODUCT(--(Input_WC!T3:T28,Input_WC!X3:X28=$B$1),--(Input_WC!R3:R28,Input_WC!V3:V28=$A14),--(Input_WC!U3:U28,Input_WC!Y3:Y28=$B$2),Input_WC!S3 :S28,Input_WC!W3:W28) For clarity (attempt): =SUMPRODUCT(--(invoicecolumn1,invoicecolumn2=$B$1),--(itemcolumn1,itemcolumn2=$A14),--(contractorcolumn1,contractorcolumn2=$B$2),qtycolu mn1,qtycolumn2) Am I doing this wrong? Thanks so far, and anymore ideas would be appreciated! -- yesac142 ------------------------------------------------------------------------ yesac142's Profile: http://www.excelforum.com/member.php...o&userid=24391 View this thread: http://www.excelforum.com/showthread...hreadid=379938 |
#5
|
|||
|
|||
this may be a second response
the sumproduct has to have contiguous arrays in the sections the simple way to fix your equation is to use two sumproducts in your equation "yesac142" wrote: I get the #VALUE! error when I tried the SUMPRODUCT function. I worked for summing one set of data, but when I try to combine sets it doesn't. =SUMPRODUCT(--(Input_WC!T3:T28,Input_WC!X3:X28=$B$1),--(Input_WC!R3:R28,Input_WC!V3:V28=$A14),--(Input_WC!U3:U28,Input_WC!Y3:Y28=$B$2),Input_WC!S3 :S28,Input_WC!W3:W28) For clarity (attempt): =SUMPRODUCT(--(invoicecolumn1,invoicecolumn2=$B$1),--(itemcolumn1,itemcolumn2=$A14),--(contractorcolumn1,contractorcolumn2=$B$2),qtycolu mn1,qtycolumn2) Am I doing this wrong? Thanks so far, and anymore ideas would be appreciated! -- yesac142 ------------------------------------------------------------------------ yesac142's Profile: http://www.excelforum.com/member.php...o&userid=24391 View this thread: http://www.excelforum.com/showthread...hreadid=379938 |
#6
|
|||
|
|||
Thanks, I'm getting closer I think. However, I'm not sure that I've been clear enough (more detail below). The examples are for 2 sets of columns... I actually have 58 sets to conditionally sum. I suppose that I could write a huge formula and include all the sets. I think that SUMPRODUCT will handle 30 at a time. The following explains 1 set of columns, which is repeated 58 times. The sheet will need to handle at least 250 rows (not related to no. of items). ITEM = (250 items) the pay item number, revenue and cost associated QUANTITY = (number) the amount of the ITEM to be billed INVOICE = (number) period (month) in which to bill the QUANTITY CONTRACTOR = (5 different ones) company responsible for the work I'm no good with VB/macros, but all ideas are appreciated. Thank you for your help and patience. -- yesac142 ------------------------------------------------------------------------ yesac142's Profile: http://www.excelforum.com/member.php...o&userid=24391 View this thread: http://www.excelforum.com/showthread...hreadid=379938 |
#7
|
|||
|
|||
One method which may work for you. add a work sheet (Helper sheet)
If your main sheet has less than 300 rows and is called Input_WC! and starts in column R in the helper sheet A1 =offset(Input_WC!$A$1,mod(Row(),300)-1,floor((Row()-1)/300)+20) in B1 =offset(Input_WC!$A$1,mod(Row(),300)-1,floor((Row()-1)/300)+18) in C1 =offset(Input_WC!$A$1,mod(Row(),300)-1,floor((Row()-1)/300)+21) in D1 =offset(Input_WC!$A$1,mod(Row(),300)-1,floor((Row()-1)/300)+23) Note don't be surprized if you have to play with the equations a bit to get them to reference the right columns in your master File It might also be worth your while to manipulate your master sheet just a touch so that the column references can be regular. since you have 58 sets copy A1:D1to A15600:D15600 (Simplest way to do this is to select and copy A1:D1 go to the little box to the left of the formula bar and enter A1:D1560 enter to select the cells and then paste. Now do your sumproduct as =SUMPRODUCT(--(Helper!A1:A15600=$B$1),--(Helper!B1:B15600=$A14),--(Helper!C1:C15600=$B$2),Helper!D1:D15600) "yesac142" wrote: Thanks, I'm getting closer I think. However, I'm not sure that I've been clear enough (more detail below). The examples are for 2 sets of columns... I actually have 58 sets to conditionally sum. I suppose that I could write a huge formula and include all the sets. I think that SUMPRODUCT will handle 30 at a time. The following explains 1 set of columns, which is repeated 58 times. The sheet will need to handle at least 250 rows (not related to no. of items). ITEM = (250 items) the pay item number, revenue and cost associated QUANTITY = (number) the amount of the ITEM to be billed INVOICE = (number) period (month) in which to bill the QUANTITY CONTRACTOR = (5 different ones) company responsible for the work I'm no good with VB/macros, but all ideas are appreciated. Thank you for your help and patience. -- yesac142 ------------------------------------------------------------------------ yesac142's Profile: http://www.excelforum.com/member.php...o&userid=24391 View this thread: http://www.excelforum.com/showthread...hreadid=379938 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Source Name Not Found | Excel Worksheet Functions | |||
Macro to search for and display data in another worksheet | Excel Worksheet Functions | |||
Import Data Keeps asking for Password | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |