 # Calculation based on three different criterias

• Hi everyone,
I have ran into some trouble with my formula to calculate the total weight of a certain item in my cost estimator worksheet.

I have attached a file and the formula that I'm having issues with is Column I (Weight)..

What I would like the formula to do is calculate the weight based on three different criterias..

1) If Columns B & C are blank I would like Column I to Display a "0". (which it does right now)

2) If Columns B & C are found in my Weight Tab then it needs to calculate the total weight in Column I. (which it also does right now)

3) If Columns B & C are not found in my Weights Tab and are not blank to still do the calculation as weight/ft and Net Cost are entered into the worksheet...

The reason for the 3rd criteria is its almost impossible to enter all types of sizes for some of the items
that we work with (very rare items that are abnormal sizes) so if something is entered that is rare i still need the calculation to happen.

Thank you for your help on this in advance..
Hope that this is possible,

forum.ozgrid.com/index.php?attachment/38173/

## Files

• Re: Calculation based on three different criterias

I am sending back the file . see formula in I10 which is proxy for I3
=IF(ISNA(VLOOKUP(B3&C3,Weight!C1:D9,2,0)),0,VLOOKUP(B3&C3,Weight!C1:D9,2,0)*E3)

copy down.

Is this what you want.

## Files

I am not an expert. better solutions may be available. [email protected]\$\$\$gmail.com

• Re: Calculation based on three different criterias

Not quite. it calculates it yes... but it still pulls the weight from the weights page.. I need it to use the formula that is the last part of my formula...

=IF(ISNA(VLOOKUP(B2&C2,Weight!\$C:\$D,2,0)),"0",(A2)*(E2+F2/12)*(G2))

Otherwise it does exactly what my original formula does...

The problem is once it finds that the logical test is true being if it doesnt find whatever is in B2 and in C2 in the weight page it puts a zero.. i need it to put a zero there only if the columns are blank.. and if they are not blank to still do the rest of the formula (A2)*(E2+F2/12)*(G2)) with what ever weird numbers i plug in to G2...

Not sure if any of this makes any sence..

• Re: Calculation based on three different criterias

zvldak

Is this what you want?

=IF(AND(B2="",C2=""),0,IF(ISNA(VLOOKUP(B2&C2,Weight!\$C\$2:\$D\$9,2,FALSE)),A2*(E2+F2/12)*(G2),VLOOKUP(B2&C2,Weight!\$C\$2:\$D\$9,2,FALSE)))

Windy

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!