Summing data

[yesac142]

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!

 

[Guest]

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]

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),qtycolumn1,qtycolumn2)

Am I doing this wrong? Thanks so far, and anymore ideas would be
appreciated!

 

[Guest]

I dont believe you can have the (range,range) format
you will probably have to us
=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)

o
=SUMPRODUCT(--(and(Input_WC!T3:X28=$B$1,or(column(Input_WC!T3:X28)=20,column(Input_WC!T3:X28)=25))),--(Input_WC!R3:V28=$A14),--(Input_WC!U3:Y28=$B$2),Input_WC!S3:W28)

 

[Guest]

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]

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.

 

[Guest]

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 A11to A1560015600
(Simplest way to do this is to select and copy A11
go to the little box to the left of the formula bar and enter A11560
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!D115600)