Using Multiple Formulas in One Cell

Hello,

I have a problem regarding a large spreadsheet where I need to have one formula that calculates the value of the cell from other data within the same line, but then have another formula that will completely wipe-out the data if certain parameters are met.

To be specific, the cell in question gets its value from 14 other cells that all exist in the same line item. On some occasions though, there are duplicates of the entire line item on the very next line but shouldn't be charged because both exist under one overall contract, thus making the second calculated cell in the contract null.

My question is, how do I have a cell that exists based on the values of items that exist within the line, but then can be also be considered "0" if it exists as one of the duplicates under the entire umbrella of a single contract?

j3b1l1,

I'm not 100% this is the solution you're looking for. Attached is a file that shows how to use an IF statement to calculate duplicate values differently. Hope this helps!

I'm going to attach a sample workbook with anonymous values to help illustrate the problem. Be back shortly.

Here's an anonymous workbook that I think will help better display the issue.

The BEFORE sheet shows the raw data. The 'Amount Charged' column has a formula that calculates the amount each individual line item will be charged for each requirement. My intent is to be able to make multiple clients that exist under one contract not get charged more than once, as illustrated in the AFTER sheet. The amount charged is only once per contract, not once per line item. I hope this makes things clear.

I guess I should also point out the actual costs associated with each column. The "Requirement" column is $10.00 and each additional requirement is $5.00. This gives the total amount charged per line and is the basis of the formula in the "Amount Charged" column.

Based on your sample sheet

in J2

=IF(COUNTIF($B$2:B2,$B2)>1,0,5+SUM(IF($C2:$I2>0,5)))

Enter with Ctrl+Shift+Enter

Copy down

Assumes C=10, D:I=5