Sum Formula for not matching

[Daniele M.]

I created an excel worksheet for my personal billing where on column A there
is the date of the Month, column B the Voice (both for profit and for
charges), column C
the value for the profit and column D the value for the charge.I decided
that all the texts in the column B must be respect determinated values i.e.
Gasoline, Skiwear, medicine, Home tax and so on.
In this way I can use the SUMIF function in order to have a series of
Subtotals group by genre (like skiwear, Medicine, etc etc)

For example:
A B C D
1 Date Voice Profit Charge
2 1/01/03 Gasoline 30
3 Medicine 15
4 Skiwear 10
5 2/01/03 Salary 1000
6 Gasoline 20
7 Goofy x

Totali 1000 65

Subtotals
Gasoline 50
Medicine 15
Skiwear 10
Newspapers 8
Home Tax 3
Underterminated x


Till now very simple but I'd like to add the voice "Undeterminated" in the
subtotals section in order that
if in the column B does not exist any known match with the values in the
subtotals range then the correspondent
value must be put and added to the Underteminated voice.

For Example Goofy is not gasoline, Medicine, skiwear, newspapers, home tax
and so on, so the correspondent value to Goofy in the column D (value x)
must be added in the column of Subtotals in correspondence of the line
Underterminated
I hope to be clearer then before.

Many Thanks Daniele

I tried with concatenated IF but I have the limit of 7.

 

[Ken Wright]

You can actually do this quite easily by using the formulas you already have. You know how much
the total list comes to, and you can also calculate how much the total of your
Gasoline/Medicine/Skiwear etc comes to. The delta between those two numbers is your undetermined
figure.

That having been said, I would have thought your data cries out for a Pivot table - It would make
analysing this stuff a breeze:-

Debra Dalgleish has a good intro to Pivot tables:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm