Hi Gary,
Glad to hear you got this working. If you want to add additional criteria you can expand on the SUMPRODUCT finction. So if you wanted to incorporate the values in column A then use:
=SUMPRODUCT((A1:A10="687196004-9")*(B1:B10="83-025")*(D1
10))
Below is a short write-up on this function:
Good Luck,
Mark Graesser
(e-mail address removed)
Our Fair City - Boston MA
Sum Product:
The SUMPRODUCT function will create an array of values for each argument. The corresponding components of each array are then multiplied, and these products are added.
The arrays must be of the same size, and in the same direction (vertical or horizontal). However, they do not have to be level. One can use an array of A1:A5 in one argument and an array of B11:B15 in another argument. Arrays of conflicting size will produce a #N/A error.
The use of a single multiplier is also acceptable. SUMPRODUCT( (A1:A5) * 5 )
For conditional arguments the return is a 0 if false and a 1 if true
Non-conditional arguments, values used directly from the spread sheet must be numeric. Text arguments will return a #VALUE error.
Sample Problem:
With the following table in A1:C9
A D 1
A E 2
A F 3
B D 4
B E 5
B F 6
C D 7
C E 8
C F 9
And the formula:
=SUMPRODUCT( (A1:A9=â€Bâ€) * (B1:B9=â€Eâ€) * (C1:C9) )
The resultant arrays produced are:
(0,0,0,1,1,1,0,0,0) * (0,1,0,0,1,0,0,1,0,) * (1,2,3,4,5,6,7,8,9)
The products or the corresponding components then produce:
(0,0,0,0,5,0,0,0,0)
And the final sum of these products is 5
----- gary wrote: -----
Mark,
That works great for what I posted. But will it work for
the following? (I.e., when the value in column A changes,
I need amount-subtotals for each different value in
columns B and C).
Gary
A B C D
687196004-9 19-002 2,335.30
687223007-5 68-1366 20.24
687223007-5 68-1378 2.34
687223007-5 68-1856 8.48
687223007-5 68-2232 223.68
687223007-5 68-4115 242.40
687223007-5 19-038 257.82
687223007-5 68-1366 10.12
687223007-5 68-1378 1.17
687223007-5 68-1856 4.24
687223007-5 68-2232 109.23
687223007-5 68-4115 110.80
687223007-5 19-038 131.07
687233016-4 68-2232 22.58
687233016-4 19-003 76.24
687233016-4 68-2232 27.72
687233016-4 19-003 78.24
687233016-4 68-2232 28.76
687233016-4 19-003 80.78
687252002-0 68-1366 31.38
687252002-0 68-1378 3.64
687252002-0 68-1856 8.48
687252002-0 68-2232 134.82
687252002-0 68-4115 129.12
687252002-0 19-003 465.10
687252002-0 68-1366 31.38
687252002-0 68-1378 3.64
687252002-0 68-1856 8.48
687252002-0 68-2232 139.80
687252002-0 68-4115 129.96
687252002-0 19-003 480.18
689151007-7 68-2683 60.00
689151007-7 68-2694 10.78
689151007-7 68-2695 26.58
689151007-7 17-001 513.52
689151007-7 68-1865 4.68
689151007-7 68-2683 60.00
689151007-7 68-2694 11.86
689151007-7 68-2695 30.56
689151007-7 17-001 401.74
689151007-7 68-1865 4.68
689151007-7 68-2683 60.00
689151007-7 68-2694 12.86
689151007-7 68-2695 33.62
689151007-7 17-001 416.12
689151007-7 68-1865 4.68
689151007-7 68-2683 60.00
689151007-7 68-2694 13.66
689151007-7 68-2695 40.46
689151007-7 17-001 424.04
689151007-7 68-1865 4.68
689151007-7 68-2683 60.00
689151007-7 68-2694 13.66
689151007-7 68-2695 40.46
689151007-7 68-4612 9.90
689151007-7 17-001 438.38
-----Original Message-----
Gary,
You can do this with SUMPRODUCT:
column D must contain only numeric data. You could also
replace the "83-025" with a cell reference and then enter
the code in that cell.
