Quick Copy SUMIFS

[Glisten]

Hello! I am an excel novice. So please excuse any absurdity.
I have a SUMIF formula that I'd like to repeat.

Column A states a number:
"11.00
11.51
11.98
11.99
xx.00
xx.51
xx.98
xx.99
xx.00
xx.51
xx.98
xx.99"

This pattern continues through 300:
"300.00
300.51
300.98
300.99"

I have a SUMIF formula:
SUMIFS(B:B,A:A,">=11.00",A:A,"<11.50")
SUMIFS(B:B,A:A,">=11.51",A:A,"<11.97")
SUMIFS(B:B,A:A,"11.98")
SUMIFS(B:B,A:A,"11.99")

I need to copy the formula down to 300. Currently if I drag it down, the numbers stay the same. I need the numbers to automatically update in the formula as it is copied as opposed to manually typing in the updated number:
"SUMIFS(B:B,A:A,">=11.00",A:A,"<11.50")
SUMIFS(B:B,A:A,">=11.51",A:A,"<11.97")
SUMIFS(B:B,A:A,"11.98")
SUMIFS(B:B,A:A,"11.99")
SUMIFS(B:B,A:A,">=12.00",A:A,"<12.50")
SUMIFS(B:B,A:A,">=12.51",A:A,"<12.97")
SUMIFS(B:B,A:A,"12.98")
SUMIFS(B:B,A:A,"12.99")
SUMIFS(B:B,A:A,">=13.00",A:A,"<13.50")
SUMIFS(B:B,A:A,">=13.51",A:A,"<13.97")
SUMIFS(B:B,A:A,"13.98")
SUMIFS(B:B,A:A,"13.99") so on and so forth through 300....


I've attached a spreadsheet that has all the factors in it. Thank you for any help!

[Pete_UK]

You can change the formula in C1 to this:

=SUMIFS(B:B,A:A,">="&A1,A:A,"<"&A2)

Note that it is picking up the start and end of the number range from the two adjacent values in column A, and so when it is copied down it will automatically adjust to the relevant range for each row.

I'm sure you realise that column B is formatted for time, and currently the cells all seem to contain zero, so you are not going to get very meaningful results. Also, you are not really adding times together, and the formula will only return the time for that narrow range (i.e. for the row that it is on), so perhaps an =B1 formula might be more appropriate.

Anyway, hope this helps.

Pete

[63falcondude]

Hello and welcome to the forum.

Try this in C1:

=IF(MOD(ROWS($1:1)-1,4)<2,SUMIFS(B:B,A:A,">="&A1,A:A,"<"&A2-0.01),SUMIFS(B:B,A:A,A1))

[Glisten]

I greatly appreciate your answers! Thank you!

However, they don't seem to work for what I'm doing. I'm uploading another spreadsheet that should provide a bit more context. I'm sure what I'm doing is not a great way to do it. I just don't know.

In short, I am trying to sum different times by different people that correlate to specific numbers that correlate to specific tasks. I highlighted in Yellow the formula in question. I'm need to duplicate it a ridiculous amount of times and have the numbers automatically change. Maybe it's not possible?

Thanks for any help provided.

[FDibbins]

Hi, welcome to the forum

To start with, if you are just using a single value in the test...
=SUMIFS(B:B,A:A,"13.98")
You dont need to " " around the number - that actually converts it to text. Just use
=SUMIFS(B:B,A:A,13.98)

My understanding is that you want to be able to have the test value be dynamic? What is the interval between each test value?

[Pete_UK]

Put this formula in cell N2:

=SUMIFS(B:B,A:A,">="&L2,A:A,"<"&$L3)

Then you can copy this into the cells from N3 down as far as you need to. Then you can select all those cells from N2 down and click < copy >, and then paste them successively into cells P2,R2, and T2.

Hope this helps.

Pete

[Glisten]

This is extremely close! It shifts everything a bit but works! Now that I see your solution, it seems obvious. Seriously excellent. Thank you!

[Pete_UK]

Just spotted that there was a slight omission in the formula that I gave you:

=SUMIFS(B:B,A:A,">="&$L2,A:A,"<"&$L3)

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete