find cells based on multiple critera, then add them

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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

?
 
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.
 
Back
Top