Excel 2016 VBA to generate table using SUMIFS (or appropriate) with multiple criteria

Hi guys,

I really need some help because what I wish to achieve is out of my skill set. I have a table of data to be updated with information as orders are sent; this table has columns for a start date, an end date, a location (country), and a number (of items sent to the location). I have made the location column a named range titled "ORDERS".

I have a second table of data with a column for a list of countries and a column results to indicate a '1' if the criteria matches and a '0' if the criteria does not match. I have made the countries column a named range titled "COUNTRIES". The sheet which holds the tables is called "Control".

I need to achieve two outcomes from the data.

First, I want to be able to enter a date into a cell, lets say $T$5 and when the macro runs to first check that the date is >= start date and <= end date (ie in range); then to check the name of the location and if the number is > 0 to populate the results column with a '1'.

Second, I want to SUM the data using the same criteria as above (effective date from cell $T$5 to be in range, and for each iteration of the location to sum the number). Locations will appear in multiple rows, hence the requirement to SUM when the criteria fits. I wish for the active data, ie countries that have appeared in date and with a SUM > 0 to appear in a separate and generated table on another sheet within the workbook titled "MainMap", just two columns Country and No. of Orders. If possible the macro would ideally delete a previously generated table (within sheet "MainMap") so that fresh data appears each time.

Can anyone help me to achieve my aims? Incidentally this will feed into the macro I have created which uses the 1s and 0s from results to change the colour of countries on an EMF of a world map, which is located on "MainMap".

Many thanks in advance

Tony

Thanks for the response. I have attached the sample to be looked at.

I have entered a RANDBETWEEN formula in the Results column, if you use the command button on the "MainMap" sheet you will see how the macro currently functions.

All help is greatly appreciated.

Partly correct yes.

As well as the table on the MainMap page being dynamic and updating when the macro is run, I also wish for the table on the Control page to update (columns A and B) - column B (Results) to have a 1 when the country appears in the table on the MainMap page. It is this table that drives the colours on the MainMap to be updated.

:D cheers for looking into this for me.

You good sir are an uber-legend.

Many thanks :D