Forum Discussion
Solved
Nested IF function with Sums, and Greater Than
Hi Everyone,
I am trying to have an IF formula work with multiple criteria and I keep getting Zero Value. I am trying to put the formula in column "P" Total "A"/"B" Commission
Essentially if "Combined Rate" is "Yes" I would like the formula to do ('Company "A" Commission' + 'Company B Commission') if that amount is greater than "Minimum" if amount is not greater than "Minimum" I want the "Minimum". If "Combined Rate" is "No" I would like the formula to do "Total Company "A"' + "Total Company "B"" this is the formula I have right now that isn't bringing in "0" values.
=SUM(IF(Q2="Yes",IF(H2+M2>I2,H2+M2,I2),J2+O2))
Thank you!
Hello,
that sample file is really helpful. I can suggest two approaches:
Approach 1: use a helper column where you combine all the lookup criteria into a lookup key. Insert a new column after F and in the new column G enter this formula in row 2 and copy down:
=A2&B2&C2&E2
It combines all the values to one lookup key. Now you can refer to that lookup key in an Index/Match combination formula in cell M2:
=INDEX($F$2:$F$13,MATCH(I2&J2&K2&L2,$G$2:$G$13,0))
Or, if you don't want to use the helper column, you can do it all in one step, but the result may take a while to calculate if you have many rows of data. I'm still taking into account that a column G was added, so the cell references are a bit different from your original layout, but you can see it working in the attached file. In cell M3 I use the formula
=INDEX($F$2:$F$13,MATCH(I3&J3&K3&L3,INDEX($A$2:$A$13&$B$2:$B$13&$C$2:$C$13&E2:E13,0),0))
File is attached. Let me know if that helped.
2 Replies
Hi Mary,
Formula shall work if all your cells are in number format. Strange that zeroes in some columns are on left side of the cells, that usually means the cells are in text format. Anyway, without the sample it's hard to say.
And in your formula SUM is not required,
=IF(Q2="Yes",IF(H2+M2>I2,H2+M2,I2),J2+O2)
is enough
Thank you!
