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
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
Tryand fill across.Formula:=SUMPRODUCT(B4:B18,--(--LEFT($A$4:$A$18,5)>=1215),--(--LEFT($A$4:$A$18,5)<=1290))
Dave
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")) .
I tried the both of this formula but it doesn't work as column A contains number and text. can you please try?
=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"))
Thank you so much Dave. Genius.
You are welcome. Thank you for the feedback, added rep and marking your thread Solved.
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.
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.
Thanks you so much hrlngrv..good to know the other ways to do it.
I had same results as you.
Wow! Thanks for that hrlngrv. I had no idea FILTER would let us "cheat" with mixed strings like that.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks