find cells based on multiple critera, then add them

R

Robert

Hello,
I am using the following formula successfully to find the data in sheet 1
and bring it to sheet 2
{=IF(ISNA(MATCH(1,(RTH!$A$1:$A$500=$A5)*(RTH!$C$1:$C$500=$M$1),0)),"",INDEX(RTH!$F$1:$F$500,MATCH(1,(RTH!$A$1:$A$500=$A5)*(RTH!$C$1:$C$500=$M$1),0)))}

I now want to perform the same task, however, now I know that there will be
two cells that meeet the two criteria instead of one. The above formula seems
to grab the first one only. Ultimately, I will need to add them together as
well. What modifications will I need to make to my formula to grab both
results and what is the most proficient way to add them together?

thanks in advance for any help,
Robert
 
R

Robert

Thanks Frank! The following formula works very well
=IF(SUMPRODUCT(--(TCE!$A$1:$A$2000=$A11),--(TCE!$C$1:$C$2000=$M$1),(TCE!$D$1:$D$2000))>$E11,$E11,SUMPRODUCT(--(TCE!$A$1:$A$2000=$A11),--(TCE!$C$1:$C$2000=$M$1),(TCE!$D$1:$D$2000)))

How can I modify it to leave a blank space instead of a zero when it is not
able to match either of the 2 criterion (A11) or (M1)?

thanks in advance for any help,
Robert

?
 
R

Robert

Hello,
This works but is rather lengthy
=IF(IF(SUMPRODUCT(--(TCE!$A$1:$A$2000=$A12),--(TCE!$C$1:$C$2000=$M$1),(TCE!$D$1:$D$2000))>$E12,$E12,SUMPRODUCT(--(TCE!$A$1:$A$2000=$A12),--(TCE!$C$1:$C$2000=$M$1),(TCE!$D$1:$D$2000)))=0,"",IF(SUMPRODUCT(--(TCE!$A$1:$A$2000=$A12),--(TCE!$C$1:$C$2000=$M$1),(TCE!$D$1:$D$2000))>$E12,$E12,SUMPRODUCT(--(TCE!$A$1:$A$2000=$A12),--(TCE!$C$1:$C$2000=$M$1),(TCE!$D$1:$D$2000))))
Is there a simpler way to avoid the zero on a non match? I don't want to do
something like change the text to match the same color as the background,
since other formulas work off of this one and a zero in this cell causes
divide by zero errors in others...
thanks,
R.
 
Top