# Excel: Total of some cells equal to a specific value

#### 77ssbssb

##### Prominent
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.

Solution
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...

#### Math Geek

##### Estimable
Herald
just autosum each row and a quick glance will tell you which equals 130. simplest way anyway.

#### 77ssbssb

##### Prominent

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

#### Math Geek

##### Estimable
Herald
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
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

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
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
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

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

##### Illustrious
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
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

Let me make a example Sir:

Report One:

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

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

##### Illustrious
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?

#### 13thmonkey

##### Distinguished
Under the hood rep2 is made of many heads, you need the report to give you that detail, then it is trivial and got can get an absolute answer.

##### Splendid
Moderator
And let's not forget this iteration:
Report 1:

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

Report 2:
Charge - 1100

That makes three different ways to get to 1100 from Report 1 charges.

-Wolf sends

#### 77ssbssb

##### Prominent

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?

#### 13thmonkey

##### Distinguished
So with 60 entries there might be 100s of combinations that match.

For the final time rep2 needs to be broken down into its components, it must have these components.

#### Ralston18

##### Splendid
Moderator

Report One:

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:

New Report two:

Total Charge: 1000/-

#### 13thmonkey

##### Distinguished
Precisely, the data must exist.

Replies
0
Views
753
Replies
3
Views
1K
Replies
2
Views
3K
Replies
3
Views
2K
Replies
1
Views
2K