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))
Last edited by Batman11692003; 10-16-2011 at 04:33 PM.
Hi
You could try the following formula placed in D2 on the Main tab:
It's a little long but you have to match two criteria and two different columns.=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)
hope it helps
Thanks for your help. But sometimes I have some fields that say NA and the error I get says #value.
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=B 2)*(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
Audere est facere
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks