ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
Course Of The Month
2 days, 9 hours left to enrollBecome 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
Active today
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 today
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
Suggested Solutions
A little background as to how I came to I design this code:
Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses
Course of the Month2 days, 9 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.