Thanks for the reply, but I'm having a hard time
understanding this formula, even with Excel help.
=SUMPRODUCT(('RAW DATA'!$A$2:$A$229=$B14)*('RAW DATA'!$B$2:$B$229=$D14)
*('RAW DATA'!$C$2:$C$229>600)*('RAW DATA'!$C$2:$C$229<63999)
*INDEX('RAW DATA'!$A$2:$P$229,0,MATCH($E$12,'RAW DATA'!$A$1:$P$1,0)))
('RAW DATA'!$A$2:$A$229=$B14) is a criteria expression.
In this case, the field from the table in column A with fieldname in cell A1
(which isn't needed in hard-coded conditional sums) is on the left hand side of
=, and the criterion to match on the right hand side of =. If the field were
named Field1, this would be equivalent to the criteria range
Field1
="="&$B14
where the second line is a formula. Likewise for the next three terms. So if the
col B and C fields were named Field2 and Field3, respectively, the 4 conditional
terms together would be equivalent to the criteria range
Field1 Field2 Field3 Field3
="="&$B14 ="="&$D14 >600 <63999
The final term, the INDEX() call, evaluates to an entire field as an array. The
MATCH() call within it locates the field with fieldname matching the value of
cell E12. The results of calling INDEX() with 2nd or 3rd argument equal to zero
is mentioned in online help.
Combining criteria expressions by multiplying converts them to numbers and
effectively calculates the 'AND' result separately for corresponding entries in
each row of each of the criteria expressions. For example,
{TRUE;TRUE;FALSE;FALSE}*{TRUE;FALSE;TRUE;FALSE} = {1;1;0;0} * {1;0;1;0}
= {1;0;0;0}
So the product of the 4 criteria expressions is 1 for records that satisfy all 4
criteria and 0 for any that fail to do so, and the product of this composite
criteria array and the field of values will have nonzero values only for those
records that satisfy all 4 criteria and 0 for all other records. So the sum of
the product of the 5 terms within SUMPRODUCT is the sum of the specified field
for records mathcing the 4 criteria.
I still need to test my db for two true