Forum Discussion
Solved
Create multiple if or statement but to result in a numerical number rather than a true or false
Hi, I have a banding table with two values which results in a certain rate being charged. I then have a list of items and their values. I want to create an IF OR formula which follows, if the nu...
Please find the solution attached.
I moved the lookup table to the same sheet to make it easier for me to cell reference.
Explained:
=SUMPRODUCT(SUMIFS($H$2:$H$10,$F$2:$F$10,"<"&B2,$G$2:$G$10,">"&B2))
=SUMPRODUCT(SUMIFS(Annualraterange,Agreedvaluefromrange,"<"&Value,Agreedvaluetorange,">"&Value))
Good luck!
it's now showing FALSE
Attach a sample file and I will put the equation in to test.
Thanks
Thanks
- thanks StoneKiwi for taking the time to look this over. I have attached a sample. I think excel is limited to 7 multiple conditions within an IF statement so in the sample, may need to delete a couple.
Please find the solution attached.
I moved the lookup table to the same sheet to make it easier for me to cell reference.
Explained:
=SUMPRODUCT(SUMIFS($H$2:$H$10,$F$2:$F$10,"<"&B2,$G$2:$G$10,">"&B2))
=SUMPRODUCT(SUMIFS(Annualraterange,Agreedvaluefromrange,"<"&Value,Agreedvaluetorange,">"&Value))
Good luck!
- Absolutely amazing! Thank you so much. I hadn't considered to format it in this way.