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

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

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

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

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

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

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