VLOOKUP doesn't work in an array formula, apparently

[larryk]

I have a list of eight text items and their respective numerical values in A2:B9. There are duplicates in the item names and I am looking for value totals for some items (which I have calculated using SUMIF).

Now I would like a total of all things that I haven't got totals for (i.e. "all the rest"). I can do this in two steps by doing a bunch of =ISNA(VLOOKUP(A2,$A$11:$A$12,1,FALSE)) in column D (where A11 and A12 contain the text items that i have already counted), and then a =SUMIF(D2:D9, TRUE, B2:B9) to find the total but I'd like to do it in one step.

I read about array formulas and constructed the following but it doesn't seem to work: {=SUM(IF(ISNA(VLOOKUP(A2:A9, A11:A12, 1, FALSE)), B2:B9))}. Is my syntax wrong? Or is it because VLOOKUP uses an array as an argument anyway and that's getting messed-up by the array formula processing, or something?

I can post the example worksheet if you like (and if someone can point me to the nice web-worksheet html).

Any help much appreciated,
Larry

P.S. I did it in an array formula with a nasty CONCATENATE hack but when I transfer this to my real spreadsheet if there are any substrings or overlap then the numbers may be wrong. I'd like to do it "properly"

{=SUM(IF(ISERROR(FIND(A2:A9,CONCATENATE(A11,A12))),B2:B9))}

 

[larryk]

I found HtmlMaker

arrayformula.xls
	A	B	C	D
1	data			2-stepsolution
2	a	1		FALSE
3	b	1		FALSE
4	c	5		TRUE
5	d	5		TRUE
6	a	1		FALSE
7	b	1		FALSE
8	c	5		TRUE
9	d	5		TRUE
10	totals			20
11	a	2	<--here'sthetotalofa
12	b	2	<--here'sthetotalofb
13	others	0	<--thisshouldbethetotalofothersbutdoesn'twork!
14	concatenatehack	20
Sheet1

 

[Brian from Maui]

Now I would like a total of all things that I haven't got totals for (i.e. "all the rest").

How many criterias? Couldn't you use a not equal too?

=SUMIF(A1:A10," <> Criteria",B1:B10)

 

[just_jon]

Book1.xls
	A	B	C	D
1
2	a	1
3	b	1
4	c	5
5	d	5
6	a	1
7	b	1
8	c	5
9	d	5
10
11	a	2
12	b	2
13	others	20
Sheet5

 

[larryk]

Thanks, yes that is a solution to the example problem...

...but not an answer to my question. As soon as I transfer this back into my spreadsheet it becomes a lot more complicated.

I need to work out how to sumif where the criteria is that the text value being compared is not in a certain list.

Can you tell me why the vlookup doesn't work in {}?

Cheers,
Larry

 

[just_jon]

Use

=SUMPRODUCT(--(ISNA(MATCH(A2:A10,A11:A12,0))),B2:B10)