Sum Based On Three Criteria - Urgent Help Needed

GA81848

Board Regular
Joined
Apr 25, 2005
Messages
124
I have data held in columns A:I, column A has a product code, column B has a town name, cells C1:I1 hold a short code, cells C2:I16 hold amounts issued. In three other cells I need to enter product, town and short code and have a formula in another cell that will calculate the total amount supplied.

For example, Product 1, Belfast and AL should give me 116. I've tried using sumproduct but cannot figure out the correct formula.


Product Town Total AL CB AL CO EN AL
Product 1 Belfast 2,258 43 540 63 41 20 10
Product 2 Birmingham 10,627 20 254 1,859 760 73 102
Product 3 Bradford 3,901 11 133 88 166 21 1
Product 4 Bristol - - - - - - -
Product 5 Cambridge 3,069 - 34 - - 1 -
Product 6 Canterbury 22,902 47 791 320 114 184 -
Product 7 Cardiff 1,274 - - 11 - 54 3
Product 5 Carlisle 389 13 1 39 - 2 -
Product 6 Chelmsford 11,923 277 133 157 42 523 92
Product 7 Chester 3,640 2 109 52 118 10 23
Product 8 Croydon 2,982 0 10 51 10 31 52
Product 9 Darlington 6,523 21 34 33 10 97 21
Product 10 Dartford - - - - - - -
Product 11 Derby 2,563 1 15 68 8 34 11
Product 12 Doncaster - - - - - - -



Product Product 1
Town Belfast
Short Code AL

Total 116
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

GA81848

Board Regular
Joined
Apr 25, 2005
Messages
124
This is the formula I've tried to setup =SUMPRODUCT(--(A2:A16=C20),--(B2:B16=C21),--(C1:I1=C22),(C2:I16)) but it does not work?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
SUMPRODUCT will only accept arrays of the same size, so you need to generate the array before SUMPRODUCT receives it:

=SUMPRODUCT((A2:A16=C20) * (B2:B16=C21) * (C1:I1=C22), C2:I16)
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
Or you can use this array formula that works even if there are text values (like "-") in some cell(s).

=SUM(IF($A$2:$A$16=$C20,IF($B$2:$B$16=$C21,IF($C$1:$I$1=$C22,IF(ISNUMBER($C$2:$I$16),$C$2:$I$16)))))

Ctrl+Shift+Enter

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407

ADVERTISEMENT

oops

EDIT

shg's formula also works with "-" in a cell

sorry...:oops:
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
For SHAME, Marcelo, for SHAME!

:ROFLMAO:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,082
Latest member
sasmita

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
Top