Formula for matching

  • Thread starter Thread starter Daniele M.
  • Start date Start date
D

Daniele M.

Hello people,
I am going to be crazy if I don't find this formula that should be very
simple but maybe that I am missing something.

Suppose that I have two columns A1:A20 (simple Text) and B1:B20 (Currency)
and another range A40:A55 (text).
What I would like to do is :
If a name in A1:A20 is not present or is not equal to the list in A40:A55
then go to B56 and Sum the correspondent value that it is present on the
no-matching row in B1:B20.

Does it possible? Too much complicated?

Mnay Thanks....Daniele M.
 
For each name in A1:A20 that is not in A40:A55 compute a total using the
figures in B1:B20. Is this what you're asking?
 
Exactly, sorry for not being very clear
Aladin Akyurek said:
For each name in A1:A20 that is not in A40:A55 compute a total using the
figures in B1:B20. Is this what you're asking?
Click to expand...
 
( 1.) Select A40:A55, go to the Name Box on the Formula Bar, type List, and
hit enter.
( 2.) Insert a row before the current row 1. The data will now occupy
A2:B21. List will be in A41:A56.
( 3.) In A1 enter: Name.
( 4.) In B1 enter: Amount.
( 5.) Format A1:B1 as bold and italic.
( 6.) Leave D1 empty.
( 7.) In D2 enter: =ISNA(MATCH(A2,List,0))
( 8.) Select A1:A21 (including the label Name).
( 9.) Activate Data|Filter|Advanced Filter.
(10.) Check Copy to another location.
(11.) The box for List range should show: $A$1:$A$11
(12.) Enter $D$1:$D$2 in the box for Criteria range.
(13.) Enter $D$3 in the box for Copy to.
(14.) Check Unique records only and click OK. D4 should now show the first
name of interest.
(15.) In E4 enter & copy down:

=SUMIF($A$2:$A$11,D4,$B$2:$B$11)

Note that I chose a different destination area than you indicated in order
to prevent a contingent confusion.
 
Sorry Aladin but maybe that I was not clear. I repeat my problem with
example:
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.

I post the message again on the top of newsgroup incase that you don't
access the newsgroup for a long period.
 
Daniele - as per my answer to your other post - You already have enough data to do a total of the
entire list. You also have some subtotalled categories which you could also total. The delta
between these two totals is what you are looking for, and that requires no complicated formulas at
all.
 
Back
Top