Formula question
 

Ok... I am creating a sheet that is going to help with billing. The
problem is that for the same job we have to bill to two different "unit
codes". So I have a table that has the "job operation" the associated
"bill amount" and the associated "unit code". Can I use this table to
sum up all "RTENG" codes in cell G13 and all "EMISC" codes in cell G14?

Does that makes sense?

Thanks

Cook


--
Cook
------------------------------------------------------------------------
Cook's Profile: http://www.thecodecage.com/forumz/member.php?userid=107
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=55941

Formula question
 

hi,
your post is a tad vaque but i would suggest you look into the SUMIF function.
It will serach a range for critera such as "RTEG" and return values
associated with the criteria.
if sumif isn't what you need, post back with more details.

Regards
FSt1

"Cook" wrote:


Ok... I am creating a sheet that is going to help with billing. The
problem is that for the same job we have to bill to two different "unit
codes". So I have a table that has the "job operation" the associated
"bill amount" and the associated "unit code". Can I use this table to
sum up all "RTENG" codes in cell G13 and all "EMISC" codes in cell G14?

Does that makes sense?

Thanks

Cook


--
Cook
------------------------------------------------------------------------
Cook's Profile: http://www.thecodecage.com/forumz/member.php?userid=107
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=55941

Formula question
 

Cook wrote:
Ok... I am creating a sheet that is going to help with billing. The
problem is that for the same job we have to bill to two different "unit
codes". So I have a table that has the "job operation" the associated
"bill amount" and the associated "unit code". Can I use this table to
sum up all "RTENG" codes in cell G13 and all "EMISC" codes in cell G14?

Does that makes sense?

Thanks

Hi Cook

If you want in-place summary info like this you can try one of the two
formulas following.

With your fields in columns A, B, and C, respectively, the sum of bill
amount for all RTENG unit codes is

=SUMIF(C:C,"RTENG")

But maybe you really need to sum only within a single job operation?
This is different because you need to match two conditions:

=SUMPRODUCT(--(A1:A99=A13),--(C1:C99="RTENG"),(B1:B99))

Where 99 is a reasonably high number that will capture all the rows of
your data.

Another way is to obtain the summary information using a pivot table.
Select all your data, start the pivot table wizard, put unit code in the
row or column area, put bill amount in the data area, and optionally put
job operation in the row or column area. With bill amount (the data
field) set to sum, you are done.

Formula question
 

smartin wrote:
Cook wrote:
Ok... I am creating a sheet that is going to help with billing. The
problem is that for the same job we have to bill to two different "unit
codes". So I have a table that has the "job operation" the associated
"bill amount" and the associated "unit code". Can I use this table to
sum up all "RTENG" codes in cell G13 and all "EMISC" codes in cell G14?

Does that makes sense?

Thanks

Hi Cook

If you want in-place summary info like this you can try one of the two
formulas following.

With your fields in columns A, B, and C, respectively, the sum of bill
amount for all RTENG unit codes is

Oops... correction:

=SUMIF(C:C,"RTENG",B:B)

But maybe you really need to sum only within a single job operation?
This is different because you need to match two conditions:

=SUMPRODUCT(--(A1:A99=A13),--(C1:C99="RTENG"),(B1:B99))

Where 99 is a reasonably high number that will capture all the rows of
your data.

Another way is to obtain the summary information using a pivot table.
Select all your data, start the pivot table wizard, put unit code in the
row or column area, put bill amount in the data area, and optionally put
job operation in the row or column area. With bill amount (the data
field) set to sum, you are done.