Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Question | Excel Worksheet Functions | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
Formula Question | Excel Discussion (Misc queries) | |||
Formula Question? | Excel Discussion (Misc queries) | |||
formula Question | Excel Discussion (Misc queries) |