SUMIF, SUMIFS AND, OR?? Formula assistance please

[Billion]

Good afternoon All, I am struggling with creating a formula and can not seem to find the help I need. I usually come to this site for help from the forum as a guest but this time I am stuck. I am using a SUMIF formula as follows
=SUMIF($B$4:$B$54,"BUK15526",$Q$4:Q$79) to total a sale.
However, I have a customer that I need to apply discount to if a line item of an order is above a certain value, lets say 5% if equal to or greater than £1000.00 and then 10% if equal to or greater than £5000. The closest I have got is the below but is not right.

=SUMIF($B$4:$B$54,"BUK15528",$Q$4:$Q$79)*AND($D$4:$D$100="CUSTOMER NAME")*AND($Q$4:$Q$100>=1000)*0.95*OR($Q$4:$Q$100>=5000)*0.9

Please not this is customer specific so I require the formula to still work for all other customers as the first formula does.

Thank you in advance for assisting with correcting this.

 

[Joe4]

Welcome to the Board!

If you want to SUM data based on multiple conditions, try using SUMIFS instead of SUMIF, which easily allow for multiple conditions.
See: How to use the Excel SUMIFS function | Exceljet

 

[Billion]

Hi Joe4, Thank you for replying. I have tried to follow that using SUMIFS but can not seem to get the formula working as I need it too. I am obviously no Excel expert so may be that causing the problem more than anything else. Would I need to changes the whole formula or just add SUMIFS instead of SUMIF? Any further tips or guidance are appreciated

 

[Joe4]

Can you please walk us through an actual example? This will help us to understand exactly what you are after.

The best way to do that is to post some example data, and based on that example data, show us what your expected output is.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Billion]

Thank you, I am trying to use the add in but excel has just crashed so will upload when I am able. In the meantime I will try to talk through the outcome

I currently have a formula =SUMIF($B$4:$B$54,"BUK15526",$Q$4:Q$79) That looks for a specified order number "BUKxxx" from column B. The formula then adds all the line items values from column Q to a total for the specified number which is displayed in Column L. Each line item will be associated the same BUK number if all on one single order.
Due to some discount structures I need the formula to Look at a specific customer name in column D, then look at the value of the line item in Column Q and apply a discount if the customer is correct and the line item value is above a set amount/s.
If the customer is correct and the value is equal to or above £1000.00 it needs to apply 5% discount
If the customer is correct and the value is equal to or above £5000.00 it needs to be a 10% discount.

The discounted values where applicable then needs to be returned to Column L as the total price

 

[Joe4]

Firstly, this formula looks problematic:
=SUMIF($B$4:$B$54,"BUK15526",$Q$4:Q$79)

The two ranges should be the same size. Basically, the way that it works is it first looks in row 4, and if the value in column B is "BUK15526", then include the value from Q4 in that sum. So the two ranges need to line-up and be the same size, as it is going through line-by-line when doing the sum. Otherwise, it does not make much sense (what exactly is Q79 looking at, since B79 is not included in your first range?).

So, do you want to apply this discounts if the total SUM is over those amounts?
Can we do this in two columns, one for the initial SUM, and then one for the Total price?
If we can do it in two columns, that save us from having to list the SUMIF calculation multiple times in a single formula.

 

[Billion]

Firstly, this formula looks problematic:
=SUMIF($B$4:$B$54,"BUK15526",$Q$4:Q$79)

The two ranges should be the same size. Basically, the way that it works is it first looks in row 4, and if the value in column B is "BUK15526", then include the value from Q4 in that sum. So the two ranges need to line-up and be the same size, as it is going through line-by-line when doing the sum. Otherwise, it does not make much sense (what exactly is Q79 looking at, since B79 is not included in your first range?).

So, do you want to apply this discounts if the total SUM is over those amounts?
Can we do this in two columns, one for the initial SUM, and then one for the Total price?
If we can do it in two columns, that save us from having to list the SUMIF calculation multiple times in a single formula.

The spreadsheet is ever growing and is done on a month by month basis. The error you have highlighted is an admin one, where by the editor has copied and pasted the formula from the previous month that may have only had 54 line items sold. This is easily rectifiable and generally are the same for both Q and B.
The discount needs to apply to the total sum if over the specified amounts
We would prefer not to use two columns if possible as this will then require further work to the sheet to rectify any further formulas based on the totals column.

 

[Joe4]

OK, to do it all it one formula would look something like this:

=SUMIF($B$4:$B$79,"BUK15526",$Q$4:Q$79) * IF(SUMIF($B$4:$B$79,"BUK15526",$Q$4:Q$79)>=5000,.9,IF(SUMIF($B$4:$B$79,"BUK15526",$Q$4:Q$79)>=1000,.95,1))

Note that if we could do it in two columns (and you could even hide the "helper" column so that no one sees it, then you would just have the original formula in that helper column, i.e.

=SUMIF($B$4:$B$79,"BUK15526",$Q$4:Q$79)

and if that were in, say, cell L4, then the final formula would look like:

=L4 * IF(L4>=5000,.9,IF(L4>=1000,.95,1))

Also note that the hard-coded value of "BUK15526" can be replaced with any cell reference, if you want to make the formula more dynamic.

 

[Billion]

OK, to do it all it one formula would look something like this:

=SUMIF($B$4:$B$79,"BUK15526",$Q$4:Q$79) * IF(SUMIF($B$4:$B$79,"BUK15526",$Q$4:Q$79)>=5000,.9,IF(SUMIF($B$4:$B$79,"BUK15526",$Q$4:Q$79)>=1000,.95,1))

Note that if we could do it in two columns (and you could even hide the "helper" column so that no one sees it, then you would just have the original formula in that helper column, i.e.

=SUMIF($B$4:$B$79,"BUK15526",$Q$4:Q$79)

and if that were in, say, cell L4, then the final formula would look like:

=L4 * IF(L4>=5000,.9,IF(L4>=1000,.95,1))

Also note that the hard-coded value of "BUK15526" can be replaced with any cell reference, if you want to make the formula more dynamic.

Hi Joe4,

Thank you so much. That is a great help.
The only condition I am missing is the part that makes it customer specific. Is this possible within the same formula?
I will take a look at your suggestion to simplify the formula as the administrator that uses the sheet will now have to change multiple BUK numbers as opposed to one.

 

[Joe4]

Hi Joe4,

Thank you so much. That is a great help.
The only condition I am missing is the part that makes it customer specific. Is this possible within the same formula?
I will take a look at your suggestion to simplify the formula as the administrator that uses the sheet will now have to change multiple BUK numbers as opposed to one.

Oh right, forgot about that part.

Just change all the SUMIF formulas to SUMIFS formulas, i.e.
change all these references:

SUMIF($B$4:$B$79,"BUK15526",$Q$4:Q$79)

to something like this:

SUMIFS($Q$4:Q$79,$B$4:$B$79,"BUK15526",$D$4:D$79,"customer")

where "customer" is the customer reference you want to look up.