SUMPRODUCT with COUNTIF to find unique entries

Attached is a simulation file using SUMPRODUCT AND COUNTIF.

Formula:

Please Login or Register  to view this content.

Customer ID : A2:A16
Entries Month : B2:B16
Lookup Month : F3

Problem
However if you look at the attached file, this formula does not return the correct answer.

Current Solution
I insert a new column (C) and to combine the text together.
E.g. C2 = A2&B2

Then I use the below formula,

Formula:

Please Login or Register  to view this content.

I got the correct result, but...

Question
Is it possible to have one formula instead of inserting a new column?

I tried

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

All failed...

Your advice would be greatly appreciated.

what are you actually trying to count?

@ FDibbins

Sorry I left out my objective. It is to count the number of customers visiting in the month.

As the example data show that customer "CN00062" visited twice in the month of May. So the formula is suppose to allow the calculation of unique entries.

Probably you could see the above attached file for more details?

bump.. anyone?

Attached the file again.

Do you use Excel 2003, as your profile says?

@ Fotis1991

Yes. My company, I am working in, is using Excel 2003.

They also have no intention to upgrade. Haha.

I have an array formula for this but use COUNTIFS. For some unexpected reason, i am not able to convert this using SUMPRODUCT. I'll keep trying...

Thanks Fotis1991.

An Array formula.

=SUM(IF(FREQUENCY(IF(B2:B16=F3,IF(LEN(A2:A16)>0,MATCH(A2:A16,A2:A16,0),"")),IF(B2:B16=F3,IF(LEN(A2:A16)>0,MATCH(A2:A16,A2:A16,0),"")))>0,1))

Using your posted workbook,

This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER), returns the count of unique Customer ID's for May

Please Login or Register  to view this content.

In your example, that formula returns: 4

Is that something you can work with?

Thanks Fotis and Ron. Is there anyway to solve this apart from using VBA and CSE formula? I used to use CSE Formula, but boss is not able to understand and thus I have to redo my work.

Your boss does not want helper columns, does not want CSE formula, not VBA..

I think he must invented a new program or make strongly complaints to Microsoft for these...

@ Fotis

Haha. They like to micro manage. Therefore, if they can't understand, they won't use. Oh well, hope there is way to solve this.

Thanks jason.b75 and Sunflowers,

Both of your formula work perfectly well. It is a new way of using the formula for me. I learn alot from these.

Really thank you.

@ jason.b75

I did a simulation on the formula and found that if the CustomerID does not visit on the selected month. It will return as #N/A.

Below is the attachment of the simulation.

I could not figure out on how this below formula work. Appreciate if you could also share your understanding.

Formula:

Please Login or Register  to view this content.

try:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

@ icestationzbra

Thanks, both of your formula work.

Mind if you could share on how the logic of the formula work?

It would be great if we could understand the logic.

Thanks for all of your help. I will try to understand the formula, somehow.

The formula are great. I would close this thread as solved.