# Sum Based On Three Criteria - Urgent Help Needed

#### GA81848

##### Board Regular
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

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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?

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)

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.

oops

EDIT

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

sorry...

For SHAME, Marcelo, for SHAME!

For SHAME, Marcelo, for SHAME!

I was thinking about
=SUMPRODUCT((A2:A16=C20) * (B2:B16=C21) * (C1:I1=C22)*( C2:I16))
that doesn't work

SHAME, SHAME

m (lower case)

Replies
5
Views
235
Replies
2
Views
245
Replies
2
Views
112
Replies
13
Views
1K
Replies
1
Views
155

1,203,082
Messages
6,053,419
Members
444,662
Latest member
AaronPMH

### 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?

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