Apr 05 2022 07:11 AM
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.
Apr 05 2022 11:33 AM
Solution
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:
Personally I'd go for #1, for the sake of simplicity, unless this is an overly-simplified example of the real world situation.
Apr 05 2022 11:33 AM
Solution
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:
Personally I'd go for #1, for the sake of simplicity, unless this is an overly-simplified example of the real world situation.