Repeat formulas now always working

Conway Penner

New Member
Joined
Nov 4, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I am using this formula in column E from E3 to E254. =SUMIFS($B$3:$B$53,$A$3:$A$53,D3) It is working from E3 to E48. But after E48 it doesn't solve the formula anymore. It is supposed to solve the formula all the way down to E254
 

Attachments

  • Capture Nov 4.JPG
    Capture Nov 4.JPG
    82.2 KB · Views: 13

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Not sure why you say after 48 does not work - image seems to work to row 53

and you are using D3 , as the criteria, which will increase by row as copied down
so in your example at D53 - its looking for a 20 in column A - does it have a 20 ??????

If 20 does not exist in column A - then 0 is the result of the sum of B as no match

and so as you copy down , the criteria has to exist - do you have 20.1, 20.2 etc

the image is difficult to see the row numbers and is not showing from row 3 to about row 28 any way - so no idea if criteria exists before that row - i suspect not

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
 
Upvote 0
You are running into Excel's Floating Point issue.
In column A you are adding 0.5 which is converting nicely to binary under the hood but in column D you are adding 0.1 which is not exact in binary.
Also your formulas are not consistent with only every multiple of 0.5 having the SumIfs formula and between them just referencing the value in the previous row.
This makes me think you are trying to apply grouping.

See if the XLookup approach works for you.

Rounding spreadsheet.xlsx
ABCDEFGHI
115.0$1.5615.0$1.56<--New$1.56<-- Original
215.5$1.6015.1$1.56$1.56
316.0$1.6315.2$1.56$1.56Why don't the formulas in column E from E186 to E251 work?
416.5$1.6715.3$1.56$1.56
517.0$1.7115.4$1.56$1.56
617.5$1.7515.5$1.60$1.60
718.0$1.7815.6$1.60$1.60
818.5$1.8215.7$1.60$1.60
919.0$1.8615.8$1.60$1.60
1019.5$1.9015.9$1.60$1.60
1120.0$1.9416.0$1.63$1.63
1220.5$2.0016.1$1.63$1.63
1321.0$2.0716.2$1.63$1.63
1421.5$2.1416.3$1.63$1.63
1522.0$2.2116.4$1.63$1.63
1622.5$2.2716.5$1.67$1.67
1723.0$2.3416.6$1.67$1.67
1823.5$2.4116.7$1.67$1.67
1924.0$2.4816.8$1.67$1.67
2024.5$2.5416.9$1.67$1.67
Sheet1 (2)
Cell Formulas
RangeFormula
E1:E20E1=XLOOKUP(ROUND(D1,10),$A$1:$A$51,$B$1:$B$51,,-1)
D2:D20D2=D1+0.1
A2:A20A2=A1+0.5
G1,G6,G11,G16G1=SUMIFS($B$1:$B$51,$A$1:$A$51,D1)
G2,G7,G12,G17G2=G1
G3,G8,G13,G18G3=G1
G4,G9,G14,G19G4=G1
G5,G10,G15,G20G5=G1
 
Upvote 0
Solution

Forum statistics

Threads
1,223,945
Messages
6,175,555
Members
452,652
Latest member
eduedu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top