simple DB function: trying sum & vlookup w/ array function

[dlh]

I'm trying to make Excel perform a typical database reporting function, but so far I'm striking out.

I have on 1st sheet a grid of numbers, and each row has a category label:

Please Login or Register  to view this content.

I have on 2nd sheet a list of possible category labels w/ attributes:

Please Login or Register  to view this content.

I want to write a formula to sum all the rows that have categories matching a given attribute. For example, given the attribute blue which is linked to cat1 & cat3, sum all rows that have cat1 or cat3.

I've tried to accomplish this with an array formula as follows. Say there are 50 categories in sheet2 and 100 rows in sheet1, spacing added for legibility:

Please Login or Register  to view this content.

This formula does NOT work. But I can't figure out why not, and I can't think of another way of approaching the problem. (I'm trying to not touch the structures of sheet1 & sheet2).

Can anyone tell me why the formula doesn't work? Or how to solve the problem?

[Paul]

I can tell you why it doesn't work - because the vlookup function is only testing the first cell in the range e1:e100, and since it finds a match ("blue" = "blue"), it does the TRUE part of the IF formula, which is a sum of the values in A1:D100. It's not going to sum only the rows that do match (since it only looked at the first cell in column E to begin with).

I'm not sure how to fix it, at this point, but can suggest an alternative if your category and attribute values are static (e.g. cat1 always equals blue, cat2 always equals green, etc.).

Please Login or Register  to view this content.

This will sum all the cells in A1:D100 where E1:E100 equals cat1 or cat3. It makes no mention of blue, but as I said - if your attributes always match your categories then that might not be necessary. If that information does change, this formula wouldn't be very consistent for you, and someone else might need to assist.

[dlh]

Thanks for the SUMPRODUCT solution Paul, but you're right, it's not quite enough. My idea is for the user to be able to arbitrarily add categories as their need arises; the SUMPRODUCT solution would of course have to be updated manually each time that happens. But it does have the advantage of not using superslow array computations.

New info: my original formula appears to function correctly *if* the first row of data on sheet1 has a "blue" category. If it has a non-"blue" category, the formula returns 0, and if the first row has a blank category (also a possibility) then the formula returns #N/A. Why??