Sumif and vLookup help

Occasional Contributor

Hi I am trying to use sumif and vlookup to calculate the amount to pay certain areas. On the summary page it is sorted by area and then for each applicant in the area they are assigned £500. The rest of the budget is then assigned per capita. It is not doing the per capita calculation as their is an error in the formula on the second sheet which it is referencing. 

 

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?

 

Sorry the data is vague, I annomoised it for GDPR but if someone can help me make this work I can add in the customer data.

1 Reply

@Gemma Telfer 

 

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:

  1. 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.
  2. 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.