#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,942
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 915
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 915
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula Question Alison[_2_] Excel Worksheet Functions 6 September 11th 08 01:19 AM
Newbie Question - Subtraction Formula Question [email protected] Excel Discussion (Misc queries) 3 May 5th 06 05:50 PM
Formula Question Formula Question Excel Discussion (Misc queries) 2 April 23rd 06 12:00 AM
Formula Question? zubee Excel Discussion (Misc queries) 2 September 21st 05 08:45 PM
formula Question danlinksman Excel Discussion (Misc queries) 3 January 25th 05 03:07 PM


All times are GMT +1. The time now is 11:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"