Apr 08 2021 10:01 PM
Hi,
I have the following formula that works well:
=(IF(AND(H13=1,E33=""),INDEX(Sheet1!C4:H30,MATCH((E31),Sheet1!A4:A30),MATCH(H15,Sheet1!C3:H3,0)),IF(H13=2,INDEX(Sheet1!L4:Q30,MATCH((E31+E33),Sheet1!J4:J30),MATCH(H15,Sheet1!L3:Q3,0)),"Check Entry")))
I need to add a extra expense to this formula where if H14 has 0 there is no additional expense, if H14 has 1 in it then $328 needs to be added to the sum of the above formula, if H14 has 2 or more in it then $492 needs to be added to the sum of the above formula.
Can anyone assist and help me?
Thanks in advance Scott
Apr 08 2021 10:27 PM
@Scott Miller I take your word for it that the formula works, though I believe you have quite a few unnecessary brackets in it. Anyhow, add this to the formula:
+(H14=1)*328+(H14=2)*492
and it should add the additional expenses based on what's in H14 (1 or 2).
Apr 10 2021 09:59 PM
Thanks for the add, the only thing it seems to get caught up on is if you place more than 2 in H14 then it reverts back to the answer of have nothing in H14?
Is there a way of showing the answer with 1 in H14 (which it does now) and the answer with 2 (which it does now), but leave the answer of having 2 if there is a value higher the 2?
So if you place a 6 (or any number above 2) in H14 it would only show the answer of having 2 in H14?
Cheers Scott
Apr 10 2021 10:32 PM
Solution@Scott Miller Sorry, I missed the "2 or more" part your original question. Change the latter part of the extras to :
(H14>=2)*492
Any number greater than or equal to 2 will add 492.
Apr 10 2021 10:32 PM
Solution@Scott Miller Sorry, I missed the "2 or more" part your original question. Change the latter part of the extras to :
(H14>=2)*492
Any number greater than or equal to 2 will add 492.