IFS SUMIF statement

[ericryan9]

Hi everyone,

I have a spreadsheet in which I want to assess a fee if the region is "NORAM". The fee will be 1% of the payments made or 1000, whichever is greater. There is something wrong in my statement because no matter which region I choose the 1,000 fee is being populated as opposed to only "NORAM". I'm a pretty basic user of excel so there may be an easier way in which to do this.


=IFS(COUNTA(I26:I41)=0,"0",SUMIF(I26:I41,"NORAM",K26:L41)*0.01>=1000,SUMIF(I26:I41,"NORAM",K26:L41)*0.01,SUMIF(I26:I41,"NORAM",K26:L41)*0.01<1000,1000)


Thanks for your help!

 

[Marcílio_Lobão]

Re: IFS SUMIF statement help

ericryan9, Good evening.

Try to use:

=IF(COUNTA(I26:I41)=0,0,MAX(SUMIF(I26:I41, "NORAM", K26:K41)*0.01, 1000))

Ist's working as you desire?
I hope it helps.

 

[ericryan9]

Re: IFS SUMIF statement help

Thank you very much for your help Marcílio, I am probably not explaining it correctly. The range (I26:I41) has a drop down of several regions and seems to populate the $1000 no matter what region I choose. I'd like it to only apply to the "NORAM" region. Any other suggestions?