Sum Based On Three Criteria - Urgent Help Needed

[GA81848]

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

 

[GA81848]

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]

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]

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]

oops

EDIT

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

sorry...

 

[shg]

For SHAME, Marcelo, for SHAME!

 

[Marcelo Branco]

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)