Sumif with critera between a range

[olly]

I dont know if this is possible but what i am trying to add a check box into my spreadsheet. The journal can have upto 100+ lines i need to sum the values in column B IF the value in column A is between 500 and 599 (in example below it should return a value of 20)


I have tried =SUMIF(A24:A27,"<499>600",B24:B27) as well as many others, i'm not sure if it is becasue column A is a calculation


A B Column A = this is a calculation =MID(C24,9,3)which is a text column

543 300.00 Column B - a value

539 220.00

100 200.00

500 -500.00

300 -200.00

 

[Clarity]

I would suggest adding in a helper column containing:


=if(and(A1>=500,A1<600),"Y","N")


Then your SUMIF() would be (assuming helper column is D):


=sumif(D24:D27,"Y",A24:A27)

 

[olly]

I did orginally have a helper columns, unfortunately i have to do more than just the one range, so i ended up with loads of helper columns

Cheers

 

[Clarity]

You can use SUMPRODUCT() to avoid the helper column:


=SUMPRODUCT((A24:A27>=500)*(A24:A27<600),B24:B27)

 

[olly]

Brilliant, just needed to put " " around to change it to text other than that thank you so much


=SUMPRODUCT((A24:A27>="500")*(A24:A27<"600"),B24:B27)