Forum Discussion

Copper Contributor

Jun 09, 2021

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...

excel error sample.xlsx14 KB

excel

Formulas and Functions

StoneKiwi
Jun 10, 2021
kimbirch2
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!

excel error sample solved.xlsx14 KB

kimbirch2

Copper Contributor

Jun 10, 2021

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.

StoneKiwi

Iron Contributor

Jun 10, 2021

kimbirch2

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!

excel error sample solved.xlsx14 KB

kimbirch2
Copper Contributor
Jun 10, 2021
Absolutely amazing! Thank you so much. I hadn't considered to format it in this way.
- Riny_van_Eekelen
  Platinum Contributor
  Jun 10, 2021
  kimbirch2 As a variant, I added two alternative solutions.
  
  1) With LOOKUP, though you need to sort your criteria in ascending order
  2) With XLOOKUP (if your Excel version supports it)
  excel error sample revised.xlsx15 KB
  - kimbirch2
    Copper Contributor
    Jun 15, 2021
    Amazing thanks Riny_van_Eekelen