IF & AND formula scenario with multiple criteria

[CCOliver]

Good Afternoon Everyone,

I have a spreadsheet that contains 4 lists that I am trying to pull information from 3of them in to the main list based on multiple criteria. I am having trouble with coming up with a formula to accomplish the task. The main list has over 50,000 rows and the others range from 6,000 - 8,000 rows each.

Attached is a simplified spreadsheet of what I am trying to accomplish. In column C I need to have the numbers from column I populate in column C based on the following criteria;

- if column A equals column F

- AND

- if column B is between columns G and H

- if both criteria are not met then the result would be "0"

I tried =IF((AND(A2:A7=F2:F4,B2>=G2,B2<=H2)),I2,0) but not getting the correct results. I was thinking I needed some kind of VLOOKUP included but can't wrap my head around it.

Any assistance would be greatly appreciated.

Thank you,

Charlie

[#2 · Accepted solution]

@CCOliver 

In C2:

=SUMIFS($I$2:$I$4,$F$2:$F$4,A2,$G$2:$G$4,"<="&B2,$H$2:$H$4,">="&B2)

Adjust the ranges for your actual setup, then fill down.

[#3]

@Hans Vogelaar 

Thank you! Worked perfectly!