vendredi 8 septembre 2017

Excel sumif conditional statement

I have a spreadsheet and I'm trying to create a statement for the fees based on a drop down of regions in column I.

The formula works correctly except it is assessing the "1000" fee on any region from the drop down box in column I.

Essentially what I'm trying to do is:

If the region is "NORAM" and the total is <1000 then the fee is 1000, >1000 then payments*.01. ($1,000.00 or 1% of total spend , whichever is greater)

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

Thank you!

Aucun commentaire:

Enregistrer un commentaire