VBA Countif Multicreteria Multiple ranges

**flavieng** · 10-16-2013, 05:11 AM

Hi there, I need your help to succeed !

I would like to do a VBA Macro but I am stuggling, I cannot remember my VBA classes from school !

I have loads of excel data to report...

My first column is plenty of different names
Second = dates
third : Ok / Not
Fourth: Region (Asia, EU, UK, US)

I have a short list of name ( Lets say 5)

I would first take the first name in my short list and cont the number of time it appears on the first column
Then the same zith the second
then the third
then the fourth and so on...

Thank you in advance

**:) Sixthsense :)** · 10-16-2013, 05:15 AM

Why you would like to compare it with 2nd 3rd 4th columns since they are Dates, Y/N,Region??

Any logic behind this??

**flavieng** · 10-16-2013, 05:18 AM

Sorry not columns, naes in my shortlist I meant

**:) Sixthsense :)** · 10-16-2013, 05:40 AM

Ok.. Where is your shortlist is located? And what is the expected output? Where the output needs to be stored?

Why not fomurla? A1:A10 is the data C1:C5 is the shortlist in the below formula

=SUMPRODUCT(COUNTIF(A1:A10,C1:C5))))

**flavieng** · 10-16-2013, 05:49 AM

My short list is further on the right side of the spreasheet.

What I would like to do is :

For each cells in my shortlist

If it is in the big list
If is it UK
If it is OK
If it is between this date and this date

Then make appear a result = counter 1 (or 2 or 3), depndeing on the number of time all the criteria are met.
Put it in A3 for exemple

Done

Now select the second name of the short list and do the same
Put it in a4

Done

The third name
in A5

Done

etc

till the end of my shortlist

**flavieng** · 10-16-2013, 05:53 AM

I tried with countif or SUMPRODUCT but it is not working because of the dates....
It is a count with multicreteria in different columns with 2 criteria in the same one (between 2 Dates )

**:) Sixthsense :)** · 10-16-2013, 06:11 AM

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

**flavieng** · 10-16-2013, 03:38 PM

Countif.xlsx

So as you see on the file attached:

in A, I have unlimited rows of data.

In the table on the right, I have the short list, which can change.

What I would like to do is :

Check every cells in the short list, see if it exist in the database (A) and if it exist, count + 1 if it is between the 2 date, in the specified region, with the specified product and if written ok.

So for exemple, I will select the full range from I9 to I23. The macro will take the first name Edouard, with UK and with the date, with the OK and with HAT and write 0, 0 in Helmet and 1 in Socks. Then do the same with the second name : Alibaba...and so on. till the end of the list, which could be of composed of unlimited rows and finishing always by Others. Once the first list done, the macro will do the same with the new list, composed of new data.

The full set of data in the table on the left in not filtered!

I know there is a very simple way to do it, but I cannot find my class notes and I cannot remember what I have done before.

Hope It is well explain, and I thank you very much for your help !

In