SUMIFS help Please!!!!!

[tbobolz]

Hi All,

I have a table with a Product Name in column A and a Product Price in column E.
I have a 2nd table that has a Product Name in column A, From Cost in Column B, To Cost in column C, and Mark up Cost in Column D

I wan to lookup each product in the 1st table into table 2, and if the price in table 1, falls (between or equals) the prices from table two column B and C, then I want to return column D

This formula works, but is vey slow and locks up the PC on too may lines - =SUMPRODUCT(--(Schedules!C:C>=E2),--(Schedules!B:B<=E2),--(ISNUMBER(MATCH(Schedules!A:A,I2,0))),Schedules!D:D)

This formula below works, but the value(s) .45 in the sting below is manually typed in. It does not work if I reference a cell where .45 is located.
WORKS:
=SUMIFS(Schedules!D:D,Schedules!A:A,'Pharm. Report'!I2,Schedules!B:B,"<=.45",Schedules!C:C,">=.45")

DOESN'T WORK:
=SUMIFS(Schedules!D:D,Schedules!A:A,'Pharm. Report'!I2,Schedules!B:B,"<=E2",Schedules!C:C,">=E2")

Is there a better way to write a solution for this?

Thanks for any help you can offer.
Terry

 

[DanteAmor]

Try this

=SUMIFS(Schedules!D:D,Schedules!A:A,'Pharm. Report'!I2,Schedules!B:B,"<=" & E2,Schedules!C:C,">=" & E2)

 

[Scott T]

Try

=SUMIFS(Schedules!D:D,Schedules!A:A,'Pharm. Report'!I2,Schedules!B:B,"<="&E2,Schedules!C:C,">="&E2)

 

[tbobolz]

Thank you very munch, I was so close!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.