Forum Discussion
Sumif and vLookup help
You wrote:
Could someone help with the second sheet please coloumn H. I think the problem lies with Row 6 where I need to add two areas. If would be easier if these were in one cell but I am not sure how to make it search for both values. Is this possible please or would it need to be separate columns?
You're right about the reference. You're asking the formula in cell H6 to find a reference that doesn't exist in the lookup table. There are at least two solutions that occur to me:
- I notice that you seem to be fine with combining England, Scotland, Ireland as one entry on the lookup table in the Customers tab...... Why not do the same with Norway and Portugal, and--while we're at it--any other likely or reasonable combination. You could also have separate entries for whatever countries will also be referenced singly. In other words, modify (extend) that table that VLOOKUP is referencing so that the values to be sought, whether single countries or multiples, will be found.
- Modify what is now one column (Column G) so that you have one column for each of however many the maximum might be....three columns, perhaps, and then use the FILTER function (requires the most current version of Excel) to pick the two or three rows that multiple criteria, nesting that in a SUM function.
Personally I'd go for #1, for the sake of simplicity, unless this is an overly-simplified example of the real world situation.
You wrote:
Could someone help with the second sheet please coloumn H. I think the problem lies with Row 6 where I need to add two areas. If would be easier if these were in one cell but I am not sure how to make it search for both values. Is this possible please or would it need to be separate columns?
You're right about the reference. You're asking the formula in cell H6 to find a reference that doesn't exist in the lookup table. There are at least two solutions that occur to me:
- I notice that you seem to be fine with combining England, Scotland, Ireland as one entry on the lookup table in the Customers tab...... Why not do the same with Norway and Portugal, and--while we're at it--any other likely or reasonable combination. You could also have separate entries for whatever countries will also be referenced singly. In other words, modify (extend) that table that VLOOKUP is referencing so that the values to be sought, whether single countries or multiples, will be found.
- Modify what is now one column (Column G) so that you have one column for each of however many the maximum might be....three columns, perhaps, and then use the FILTER function (requires the most current version of Excel) to pick the two or three rows that multiple criteria, nesting that in a SUM function.
Personally I'd go for #1, for the sake of simplicity, unless this is an overly-simplified example of the real world situation.