Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
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
Active 6 days ago
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
Introduction
This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results.
Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, left to enroll
622 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.