Sum IF Number is Between Certain Range

[arpanpanchal]

Hi Friends

I want to sum the columns if the number in the cell is between 1215 & 1290. I have attached the example worksheet.

Can you please assist?

Many thanks

[FlameRetired]

Try

Please Login or Register  to view this content.

and fill across.

[arpanpanchal]

Thank you so much Dave. Genius.

[FlameRetired]

You are welcome. Thank you for the feedback, added rep and marking your thread Solved.

[hrlngrv]

. . . =SUMPRODUCT(B4:B18,--(--LEFT($A$4:$A$18,5)>=1215),--(--LEFT($A$4:$A$18,5)<=1290)) . . .

OP seems to have Excel 2016, so wouldn't =SUMIFS(B4:B18,$A4:$A18,">01215",$A4:$A18,"<=01290") be simpler and easier for others to maintain? If 1215 were entered as a number in X1 and 1290 entered as a number in Y1, =SUMIFS(B4:B18,$A4:$A18,">0"&X1,$A4:$A18,"<=0"&Y1) or =SUMIFS(B4:B18,$A4:$A18,">"&TEXT(X1,"00000"),$A4:$A18,"<="&TEXT(Y1,"00000")) .

[FlameRetired]

Wasn't sure which way to jump. As and afterthought had actually set up a helper column in G --left(a4,5) to do the SUMIFS. Then wait and see OP's response. Never went further.

Thanks for the feedback, but I get 0s with all those. A4:A18 all have letters following the numbers.

[arpanpanchal]

I tried the both of this formula but it doesn't work as column A contains number and text. can you please try?

Please Login or Register  to view this content.

[FlameRetired]

I had same results as you.

[hrlngrv]

. . . but I get 0s with all those. A4:A18 all have letters following the numbers.

Oops. Good point, and I also screwed up on > rather than >=. Make those

=SUMIFS(B4:B18,$A4:$A18,">=01215 *",$A$4:$A$18,"<=01290*")

and

=SUMIFS(B4:B18,$A4:$A18,">=0"&$X1&" *",$A$4:$A$18,"<=0"&$Y1&"*")

Note: Excel seems to be EXTREMELY finicky about the space before the asterisk in the 1st comparison and no space before it in the 2nd comparison.

NBD in the long-run because the eventual FILTER function will come to the rescue, =SUM(FILTER(B4:B18,($A4:$A18>="01215")*($A4:$A18<"01291"))) where <"01291" is equivalent to <="01290" followed by any arbitrary characters.

[FlameRetired]

Wow! Thanks for that hrlngrv. I had no idea FILTER would let us "cheat" with mixed strings like that.

[arpanpanchal]

Thanks you so much hrlngrv..good to know the other ways to do it.