Multiple criteria lookup and add

[AGriffiths73]

Hi everyone, I really need some help as my brain has popped!

I have a spreadsheet with three tabs. One is a dashboard to bring together values from the other two. The purpose is to calculate the quantity of items per county so I can determine volumes needed for storage.

The sheets are:

Dashboard - all the countries combined from the two 'data' sheets, in one list sorted A-Z in col. B - and the type of device for Customer 1 in Row 3, and the type of device for Customer 2 in Row 4 - since the device types are not quite the same and I don't want to have a generic device type.
Customer 1 - device types in Row 2, countries in Col. B
Customer 2 - devices types in Row 2, countries in Col. B

On the Dashboard tab, I need a formula that will lookup the country in Col. B, then lookup the device type for Customer 1, then do the same for Customer 2 and add the two values together, so that for each county I have a total of device type in the correct cell.

A problem I have is that not all countries exist on both Customer tabs, so even when I have tried using a simple formula, and one country is in one tab but not the other, I get either N/A or 0 based on how I've constructed the formula.

Please help!

 

[etaf]

how have you constructed the formula
perhaps include an IFERROR(Formula, 0)
with a zero
so if an error on the lookup, ie NOT Found , it will replace with a zero
So you should get at least Cust1 or 2 if 1 of them is not in the table to lookup

IFERROR(Formula-cust1, 0) + IFERROR(Formula-cust2, 0)

 

[AGriffiths73]

how have you constructed the formula
perhaps include an IFERROR(Formula, 0)
with a zero
so if an error on the lookup, ie NOT Found , it will replace with a zero
So you should get at least Cust1 or 2 if 1 of them is not in the table to lookup

IFERROR(Formula-cust1, 0) + IFERROR(Formula-cust2, 0)

Wonderful!!! Thank you so much!

 

[etaf]

you are welcome