What is the best method for summing values where you want 2 value.
 

An example of this is: If F2=A1:A5, and G1=B1:B5, then sum values. I have
tried an array formula, but it does not return any results. Below is the
formula I was trying. I have used Booleon format also, but it is hit and miss.
=SUM(IF(AND($A3=Raw!$A$2:$A$297,Summary!$C$1=Raw!$ C$2:$C$297),Raw!$E$2:$E$297,0))

=Sumproduct(--(A1:A5=F2),--(B1:B5=G1),E1:E5)

would sum values in column E where in the same row, the column A value
equaled the value in F2 and the column B value equaled the value in G1

--
Regards,
Tom Ogilvy


"Susan F." <Susan wrote in message
...
An example of this is: If F2=A1:A5, and G1=B1:B5, then sum values. I have
tried an array formula, but it does not return any results. Below is the
formula I was trying. I have used Booleon format also, but it is hit and
miss.

=SUM(IF(AND($A3=Raw!$A$2:$A$297,Summary!$C$1=Raw!$ C$2:$C$297),Raw!$E$2:$E$29
7,0))

Here's one way, assuming the values to sum are in C1:C5:
=SUMPRODUCT(--(A1:A5=F2),--(B1:B5=G1),C1:C5)

This is effectively an expanded SUMIF formula that allows multiple IF
comparisons. The "--" in the formula are double unary operators that
cause Excel to evaluate the comparisons as a 0 if false and a 1 if
true. For each row, the Sumproduct evaluates the true / false 1/0 and
multiplies by the value in the "to be summed" column. If one or more
of the comparisons is false, the formula multiplies by zero, augmenting
the overall sum by zero since 0 times any number is zero.