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