Lookup and Index Formula

[mechpm]

I have a worksheet that I have to manually insert and calculate data when there is no need to. My worksheet is a change of design
cost breakdown. it separates credits so the customer can easley view and separate them. This is easer to do than it is
to explain am sure. However; here it goes







Column A line items

Column B line item description

Column C material value

Column D labor value

Coolum E labor hrs

Columns F, G, H & I Codes

What I need to do is, if F, G, H, or I contain lets say code #s 1 or 2 (credit items) then the data C and D is Calculated otherwise data is ignored
Use rows 6 thru 15 for any examples data will be calculated in c 16 and d 16

Been trying some look up and indexing type stuff but still struggling


Thx

R W
#s

[Aladin Akyurek]

Why not post some data?

If you inclined to do so, select an unused cell, type =, select 15 rows of your data including labels, hit F9, Edit|Copy what you see, and post the copied bit in the follow up.

[mechpm]

On 2002-05-26 13:11, Aladin Akyurek wrote:

Why not post some data?

If you inclined to do so, select an unused cell, type =, select 15 rows of your data including labels, hit F9, Edit|Copy what you see, and post the copied bit in the follow up.

yes, i am so inclined. however; I'm having a problem. when i hit F9 i get an error msg(formula to long). Also, could you clarify post to follow up? This is a new procedure for me

THx R W

[Aladin Akyurek]

On 2002-05-26 14:06, mechpm wrote:

yes, i am so inclined. however; I'm having a problem. when i hit F9 i get an error msg(formula to long). Also, could you clarify post to follow up? This is a new procedure for me

THx R W

Your reply quoted above is a follow up. Try first 10 rows. If you think 10 rows would be sufficient, then just post that bit. Otherwise, repeat the proc for 5 more rows.

Aladin

[mechpm]

On 2002-05-26 14:12, Aladin Akyurek wrote:

Your reply quoted above is a follow up. Try first 10 rows. If you think 10 rows would be sufficient, then just post that bit. Otherwise, repeat the proc for 5 more rows.

Aladin

{0,"LINE ITEM DESCRITION ","MAT ","LABOR ","HRS ","CODES",0,0,0;1,"116 LBS S S DUCTWORK ",466,208.35,4.5,1,2,0,0;2,"8 SIDE WALL S S GRILLS ",0,92.6,2,1,2,0,0;3,0,0,0,0,0,0,0,0;4,0,0,0,0,0,0,0,0;5,0,0,0,0,0,0,0,0;6,0,0,0,0,0,0,0,0;7,0,0,0,0,0,0,0,0;8,0,0,0,0,0, 0,0,0;9,0,0,0,0,0,0,0,0;10,0,0,0,0,0,0,0,0;0,0,0,0,0,0,0,0,0;0,0,0,0,0,0,0,0,0;0,"TOTALS ",466,300.95,0,0,0,0,0;0,0,0,0,0,0,0,0,0;0,"TOTAL CREDIT ITEMS L;ABOR ",0,0,0,0,0,0,0}

WILL THIS HELP

[Aladin Akyurek]

OK, I go the sample data.

You appear to have totals in C14 and D14 already calculated and want to have another set of calculations in C16 and D16, dependent on codes that are in columns F to I from F2 on. Right? Do these calculations include the totals in C14 and D14? And, what is the relationship between these calculations and codes in F to I?

[mechpm]

On 2002-05-26 15:01, Aladin Akyurek wrote:

OK, I go the sample data.

You appear to have totals in C14 and D14 already calculated and want to have another set of calculations in C16 and D16, dependent on codes that are in columns F to I from F2 on. Right? Do these calculations include the totals in C14 and D14? And, what is the relationship between these calculations and codes in F to I?

Yes C14 & D14 are Totals

Yes C16 & C16 would have another calculation

No sir we need to extract the values for C16 & D16 per the code in F to I

the code in F to I are more or less notes to the customer.

E G

# 1 would mean credit for Column. C
# 2 would mean credit for Column. D
# 3 would be an exsiting item no credit

ect.

we are looking to seperate the credited line items from the non credited line items.


I hope i have not confused you any futher as i often do concerning this exciting, yet perplexing subject.

thx rw



[ This Message was edited by: mechpm on 2002-05-26 15:46 ]

[Aladin Akyurek]

On 2002-05-26 15:44, mechpm wrote:

Yes C14 & D14 are Totals

Yes C16 & C16 would have another calculation

No sir we need to extract the values for C16 & D16 per the code in F to I

the code in F to I are more or less notes to the customer.

E G

# 1 would mean credit for Column. C
# 2 would mean credit for Column. D
# 3 would be an exsiting item no credit

ect.

we are looking to seperate the credited line items from the non credited line items.


I hope i have not confused you any futher as i often do concerning this exciting, yet perplexing subject.

thx rw



[ This Message was edited by: mechpm on 2002-05-26 15:46 ]

It seems we can get away with using a couple of SUMIFs.

In C16 enter:

=SUMIF(F2:F13,1,C2:C13)

In D16 enter:

=SUMIF(G2:G15,2,D2:D13)

PS. Could you edit your post containing the sample by giving an enter after each semicolon (";")? The whole thread would become more readable.

Aladin