What is wrong with my IF compare statement?

**Idz21** · 03-09-2010, 05:17 PM

Can someone tell me what is wrong with this formula? Is it too ambiguous? If so, how can I make it more specific?

The formula is on Sheet 3

=IF(E:E <> 'Sheet2'!C:C,SUMIF('Sheet1'!E:E,E:E,'Sheet1'!C:C),0)

What I'm trying to do is pull in $ values from Sheet 1, into Sheet 3, if the deduction code for this $ value doesn't exist on Sheet 2.

**martindwilson** · 03-09-2010, 05:55 PM

well.... that will never work post a workbook showing expected results

**Idz21** · 03-09-2010, 09:43 PM

Originally Posted by martindwilson

well.... that will never work post a workbook showing expected results

Attached is the Sample Spreadsheet. If you look in Sheet 3, you'll see the expected result for Column B (highlighted in grey). What needs to happen is that anytime a Deduction Label exists in Sheet 1, and does not exist in Sheet 2, then I need to replicate this deduction in Sheet 3.

**ConneXionLost** · 03-10-2010, 12:17 AM

Try this:

=IF(COUNTIF(Sheet2!$B$1:$B$20,C2),0,INDEX(Sheet1!$A$1:$C$20,MATCH(C2,Sheet1!$C$1:$C$20,0),1))

Amend the ranges as necessary, and copy down.

Cheers,

**TonyB51** · 03-10-2010, 12:23 AM

Hi,

I have edited you cells in column B on sheet 3 and re-attached your sample.

=IF(COUNTIF(Sheet2!B:B,C5)=0,SUMIF(Sheet1!C:C,C5,Sheet1!A:A),0)

It works.

Cheers

Tony

**Idz21** · 03-10-2010, 09:49 AM

Originally Posted by TonyB51

Hi,

I have edited you cells in column B on sheet 3 and re-attached your sample.

=IF(COUNTIF(Sheet2!B:B,C5)=0,SUMIF(Sheet1!C:C,C5,Sheet1!A:A),0)

It works.

Cheers

Tony

Thanks Tony! That worked for me. Appreciate the help.

ConneXionLost - Thanks for posting your suggestion as well. If I find a scenario where Tony's solution no longer applies, I'll plug yours in and give it a shot.