Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Solved
Sum rows if greater than X
Posted on 2012-12-26
Experts,
I have a spreadsheet with many columns and only 1 row of data
If the sum of the numbers is >=500 then combine those numbers and put the result in the cell.
Keep doing this until you reach the last column.
I am trying to avoid a manual process and increase my efficiency.
Right now, I have to manually look at the data and sum if >500.
The spreadsheet is a scaled down version.
maybe there is some canned function in excel for what I am after.
thank you.
sum500.xls
I have a spreadsheet with many columns and only 1 row of data
If the sum of the numbers is >=500 then combine those numbers and put the result in the cell.
Keep doing this until you reach the last column.
I am trying to avoid a manual process and increase my efficiency.
Right now, I have to manually look at the data and sum if >500.
The spreadsheet is a scaled down version.
maybe there is some canned function in excel for what I am after.
thank you.
sum500.xls
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
5 Comments
I would think I would do this with some vba function...
not tested, but this should be about it ..
not tested, but this should be about it ..
dim col as long
dim cols as long
dim sum_cells as long
cols = sheet1.usedrange.columns.count
col1 = 1
sum_cells = 0
while col1 <= cols
sum_cells = sum_cells + sheet1.cells(col1, 1).value2
if sum_cells >= 500 then
shee1.cells(col1, 2) = sum_cells
sum_cells = 0
end if
col1 = col1 + 1
wend
I don't really like this, but I'm not quite clear why you want to do what you're doing, so this achieves the same results as you have:
See the attached file for the context.
You could make it more efficient by adding an extra row to do the intermediate calculations, but that's probably not an issue as you're clearly on Excel 2003 or earlier so only have a max of 256 columns.
Note the dollar signs - this anchors the start of the ranges to the first column so that you can safely drag the formulas across.
sum500.xls
=IF(SUM($A1:B1)-SUM($A5:A5)>=500,SUM($A1:B1)-SUM($A5:A5),"")
See the attached file for the context.
You could make it more efficient by adding an extra row to do the intermediate calculations, but that's probably not an issue as you're clearly on Excel 2003 or earlier so only have a max of 256 columns.
Note the dollar signs - this anchors the start of the ranges to the first column so that you can safely drag the formulas across.
sum500.xls
andrew: that is pretty crafty. It seems to work just fine.
Angell: I do get an invalid outside procedure and the debugger highlights Sheet1 in the following line:
cols = Sheet1.UsedRange.Columns.Count
I am not certain how to use it though.
thank you....
Angell: I do get an invalid outside procedure and the debugger highlights Sheet1 in the following line:
cols = Sheet1.UsedRange.Columns.Count
I am not certain how to use it though.
thank you....
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11).
The larger templa…
Suggested Courses
Course of the Month11 days, 16 hours left to enroll
730 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.