Excel: Total of some cells equal to a specific value

77ssbssb

Prominent
Nov 12, 2017
16
0
560
0
Hello everyone,

I have an excel spreadsheet in which there are some random numbers available and I want to find out the specific total.
Let's say I have 4 numbers in colmun 80, 40, 20, 90 in separate rows and I want to find out which rows add to 130.

Any help on this?

PS: I am a beginner in excel 2003.
 

Ralston18

Dignified
Moderator
Might be one those situations where Excel continues to be applied while the circumstances truly require a database. To at least generate the reports.

Data volume increases, computations get more involved, reports added, there are more and more special requirements, etc. all mandating Excel to jump through the proverbial "hoops".

A once simple spreadsheet becomes a clunge that even the author can no longer manage it.

Excel has come a long ways beyond just number crunching but Excel is not a database.

But all so many people find "database" to be too intimidating, scary, confusing, etc.. And generally unwilling to make any effort to step up and to learn easier and more productive, flexible, and expandable products. Not even to make their own work and life easier. Or less stressful perhaps.

Or just too afraid of change. Or even worse, the powers that be won't permit the risk. Even if parallel systems are run to permit full testing and validation. Sad.

Yet overall one can use Excel tables as the "back end" and Access as the "front end" to do all so many things much easier. Very straightforward to link data or import/export between Excel and Access.

Have made a few converts along the way - but many not. First problem with a database and they regressed....

Some sample spreadsheets, as has been suggested/requested would be helpful.



 

77ssbssb

Prominent
Nov 12, 2017
16
0
560
0


Thanks, Autosum calculates total rows sum. I want to know the rows whose total equals to 130.
 

Math Geek

Estimable
Herald
Oct 15, 2014
517
0
5,960
139
then you want more than a basic feature. been a while for me but you're looking at if/then type statements if i recall right. should be able to autosum it and then have it highlight the row or something if it equals the right number.

don't want to do your homework for you so i'll let you go from there :)
 

13thmonkey

Distinguished
Moderator
Jan 10, 2006
799
0
19,210
87
That's not trivial, in fact I'm not sure it can be done in Excel without doing it the very long way of assessing all combinations, one combination per column, hide all of those columns (10's of them at least), and then use the result of each column to determine flags that highlight which ones add up to 130. I'd do it in VBA, even then it'd be tricky, what it none of the combinations add up to 130.

Interested to see if anyone else has a solution to this. This is nasty.

Are you sure you've understood the question correctly. (out of N numbers which M numbers from that set sum to X)
 

77ssbssb

Prominent
Nov 12, 2017
16
0
560
0


Thanks, been searching for any video tutorial which will help me as I am a beginner, but couldn't find any one which will help my case of finding out the rows... :(

 

Math Geek

Estimable
Herald
Oct 15, 2014
517
0
5,960
139
can't say i have ever tried such a thing myself but i do recall balancing type activities where you could use an if/then after summing a column to get it to maybe turn red if it was a negative number or some other special thing to draw your attention to it.

that type thing is what am thinking here except instead of positive and negative, it would be equal to or not equal to his target number. may change how to do it a lot, i'm not sure there. but i do recall doing this in excel years ago in some class or other.
only idea i have really as i never had to go deeper into excel like VBA or scripts.
 

13thmonkey

Distinguished
Moderator
Jan 10, 2006
799
0
19,210
87
You need to be able to loop, so that you can iterate and get closer to an answer. If you can iterate you can have a first stab at it, find out what the difference is, test combinations to see if any of the remaining numbers combined with a current number creates this difference.

Like I said this is nasty, it's not basic excel, i'm very very very experienced with Excel, it'd take me 30mins of hard thinking just to figure out how to do it, never mind how to then make that happen.
 

77ssbssb

Prominent
Nov 12, 2017
16
0
560
0


Sir, actually the requirement is for my office report purpose where in I have two reports, report one in which various expense heads are mentioned and in report two there is cumulative total of some of the heads from report 1. I wanted to find out the heads from report one whose total were mentioned in report two

 

USAFRet

Splendid
Moderator
Yeah, this is non trivial.

Put all the values into an array, and start combining in different combinations.
1 + 2, 1 + 3, 1 + 4, 2 + 3, 2 + 4, 1 + 2 + 3, 1 + 2 + 4, etc, etc etc.

When you hit the magic number, stop.

Whats the use case for this? There may be a different way to skin the cat.
 

13thmonkey

Distinguished
Moderator
Jan 10, 2006
799
0
19,210
87
you need report 2 broken down, with reference numbers, then you can cross match, else you are just guessing that some numbers from rep1 happen to match rep2, but that could be blind luck.
 

77ssbssb

Prominent
Nov 12, 2017
16
0
560
0


Let me make a example Sir:

Report One:
Charge head 1: 200/-
Charge Head 2: 500/-
Charge Head 3: 800/-

Report two:

Charge: 1000/-

Now I want to find out which heads were added in report one to achieve 1000/- in report 2?
 

13thmonkey

Distinguished
Moderator
Jan 10, 2006
799
0
19,210
87


If you manage to make them match it's not an important answer as you don't know if they are correctly matching. One you hit more than maybe 20items in report 1 there will be many ways to reach the number in report 2,which is correct?
 

USAFRet

Splendid
Moderator


That sounds like an error in capturing the data.

"some of the heads"...but not all.
Which ones and why?


Consider this....
Report 1:

1 - 500
2 - 300
3 - 800
4 - 600

Report 2:
Charge - 1100

There are 2 different ways to get to 1100 from the Report 1 numbers. Which one is the correct answer?
 

77ssbssb

Prominent
Nov 12, 2017
16
0
560
0


Sir, Actually my report one will be having about 60 entries and from that I have to get total of one entry and they will be not be having similar sums that I am sure of. So, considering that the sum will be not be more than once in report 1 what will be the process to get the heads?
 

Ralston18

Dignified
Moderator
In your example:

Report One:
Charge head 1: 200/-
Charge Head 2: 500/-
Charge Head 3: 800/-

Original Report two:

Charge: 1000/-

==========


Why not just have Report 2 list the "heads" that were somehow identified and totaled to appear in Report 2?

As I understand it, all the necessary selections, logic, and calculations are already being done. Just need to capture and include the labels as part of the selection process.

E.g:

Report One:

Charge head 1: 200/-
Charge Head 2: 500/-
Charge Head 3: 800/-

New Report two:

Charge Head 1: 200/-
Charge Head 3: 800/-

Total Charge: 1000/-

 
Thread starter Similar threads Forum Replies Date
B Apps General Discussion 2
N Apps General Discussion 1
S Apps General Discussion 5
N Apps General Discussion 2
L Apps General Discussion 5
B Apps General Discussion 4
K Apps General Discussion 1
R Apps General Discussion 1
J Apps General Discussion 11
E Apps General Discussion 15
L Apps General Discussion 5
S Apps General Discussion 1
A Apps General Discussion 1
E Apps General Discussion 1
T Apps General Discussion 1
B Apps General Discussion 1
B Apps General Discussion 23
B Apps General Discussion 2
H Apps General Discussion 13
Christopher Chung Apps General Discussion 4

ASK THE COMMUNITY