Excel 2007 : If statement and sumif

[Batman11692003]

How would I create a if statement in the main tab in cell D2 for stock , by calling the information from tab cost by comparing Supply and Inventory and by matching the type and error for both tab.

If Inventory is greater or equal to Supply should how a 0
If Inventory is less than Supply it should how a 1

I assume you use something like this but not sure where you add the if statement condition.

=SUM(SUMIFS(Cost!$C$2:$C$7,Cost!$A$2:$A$7,B2,Cost!$B$2:$B$7,C2),SUMIFS(Cost!$D$2:$D$7,Cost!$A$2:$A$7,B2,Cost!$B$2:$B$7,C2))

[DBY]

Hi
You could try the following formula placed in D2 on the Main tab:

=IF(SUMPRODUCT((Cost!$A$2:$A$8=B2)*(Cost!$B$2:$B$8=C2)*Cost!$D$2:$D$8)>=SUMPRODUCT((Cost!$A$2:$A$8=B2)*(Cost!$B$2:$B$8=C2)*Cost!$C$2:$C$8),0,1)

It's a little long but you have to match two criteria and two different columns.

hope it helps

[Batman11692003]

Thanks for your help. But sometimes I have some fields that say NA and the error I get says #value.

[daddylonglegs]

Try replacing the last * in each SUMPRODUCT function with a comma, like this

=IF(SUMPRODUCT((Cost!$A$2:$A$8=B2)*(Cost!$B$2:$B$8=C2),Cost!$C$2:$C$8)>=SUMPRODUCT((Cost!$A$2:$A$8=B2)*(Cost!$B$2:$B$8=C2),Cost!$E$2:$E$8),0,1)

or revert to SUMIFS

=(SUMIFS(Cost!C$2:C$8,Cost!A$2:A$8,B2,Cost!B$2:B$8,C2)<SUMIFS(Cost!E$2:E$8,Cost!A$2:A$8,B2,Cost!B$2:B$8,C2))+0

[Batman11692003]

Your function worked perfectly but what if there were no values plugged in for some of the cells in Cost!$C$2:$C$8 and Cost!$E$2:$E$8, I don't want the function to show up as 0 or 1. I just what the cell to show up blank