array formula or sumif or sumproduct

methody

Well-known Member
Joined
Jun 17, 2002
Messages
857
Hello
In column A I have sales figures. The range is named 'sales'. In column 2 I have postcodes. The range is named 'post'. I am trying to find a formula which will add all the sales which have a post code of BT5 or BT6 or BT7. I thought it was an array formula but I cant get it to work. The formula tried was:

SUM((sales)*(OR(postcode="bt5",postcode="bt6",postcode="bt7")))

I have the curly brackets entered threough 'control-shift-enter'. Is the OR a problem or brackets or should I be using a different formula?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Thank you very much. That works perfectly for what I asked you. The only wee problem I have is that sometimes the sale has no post code and it throws the formulas out. The range 'postcode' is dynamic and will have the same cells in it as sales even if there is no post code.
Any thoughts
 
Upvote 0
methody said:
Thank you very much. That works perfectly for what I asked you. The only wee problem I have is that sometimes the sale has no post code and it throws the formulas out. The range 'postcode' is dynamic and will have the same cells in it as sales even if there is no post code.
Any thoughts

What does "sometimes the sale has no post code and it throws the formulas out" mean?
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top