Number of different dates based on multiple criteria

**danielfr** · 11-28-2021, 04:40 AM

Hi,

I am looking for the following formula:

In sheet1, column "A", I have a list of different names. Those names are not unique. In sheet1, column "B", I have a list of dates. Those dates are also not unique and
a same name can have the same dates and different dates.
In sheet2, column "A", I have a list of unique names from Sheet1, column "A". Now, I would like to have in sheet2, column "B", for each name of sheet1, column "A", how different dates they are for
every name.

Example in sheet1 John has as dates, 01/01 , 02/02, 02/02, 03/03, 04/04/, 04/04. John has 4 different dates in 6 different rows. I would like to have in sheet2, column "B", next John "4"

Thank you for your help,

Daniel

**AliGW** · 11-28-2021, 05:04 AM

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

**bsalv** · 11-28-2021, 05:31 AM

a pivottable, with a data model, is a good tool for that.
Otherwise an array formula ...

**danielfr** · 11-28-2021, 06:12 AM

Please find here the file

Daniel

**AliGW** · 11-28-2021, 06:14 AM

Please confirm that you are using Excel 2016 and not MS365 - if you have upgraded, this will be easier.

**danielfr** · 11-28-2021, 06:24 AM

Yes I am using Excel 2016 and Excel 2019

**AliGW** · 11-28-2021, 06:26 AM

And does this particular issue need to work on Excel 2016?

**AliGW** · 11-28-2021, 06:31 AM

Try this:

=SUM(IF(A2=Sheet1!$A$2:$A$16,1/(COUNTIFS(Sheet1!$A$2:$A$16,A2,Sheet1!$B$2:$B$16,Sheet1!$B$2:$B$16)),0))

**danielfr** · 11-28-2021, 06:32 AM

Yes.
I am using Office 2016 at office.At home I have Office 2019 but I am only copying the file from office to home.

**AliGW** · 11-28-2021, 06:33 AM

See post #8.

**danielfr** · 11-28-2021, 06:38 AM

Thank you
But the formula is not accepted by Excel. Message :"There is a problem with the formula"

**AliGW** · 11-28-2021, 06:42 AM

That's because you need to change commas to semi-colons for your locale. Look at the attachment to post #10.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

**danielfr** · 11-28-2021, 06:50 AM

Hi Aligw,

Thanks so much for your patience and help

**AliGW** · 11-28-2021, 06:51 AM

Please mark as solved, etc., as requested in post #12.

You're welcome!

**danielfr** · 11-28-2021, 07:11 AM

Sorry to come back.

I have tried in my Excel file but it doesn't work. I copied it to cell B2 and did a copy paste. But it doesn't work. See file attached.

**AliGW** · 11-28-2021, 07:15 AM

Remove the @ signs before copying down.

Change this:

=SUM(IF(A2=@Sheet1!$A$2:$A$16,1/(COUNTIFS(Sheet1!$A$2:$A$16,A2,Sheet1!$B$2:$B$16,@Sheet1!$B$2:$B$16)),0))

to this:

=SUM(IF(A2=Sheet1!$A$2:$A$16,1/(COUNTIFS(Sheet1!$A$2:$A$16,A2,Sheet1!$B$2:$B$16,Sheet1!$B$2:$B$16)),0))

AliGW on MS365 Insider (Windows) 64 bit

	A	B	C
1	Unique name	Number of different dates
2	Adam	1	=SUM(IF(A2=@Sheet1!$A$2:$A$16,1/(COUNTIFS(Sheet1!$A$2:$A$16,A2,Sheet1!$B$2:$B$16,@Sheet1!$B$2:$B$16)),0))

Sheet: Sheet2

If this doesn't work, try entering the formula as an array formula before copying down (CTRL+SHIFT+ENTER).

**AliGW** · 11-28-2021, 07:38 AM

Is this resolved now?

**danielfr** · 11-28-2021, 07:39 AM

Sorry but still no luck

**AliGW** · 11-28-2021, 07:41 AM

Did you try with CSE?

The formula works on older versions of Excel. There is no reason why it should not work for you if entered correctly. This is user error, I am afraid, so very difficult to resolve for you.

AliGW on MS365 Insider (Windows) 64 bit

	A	B	C	D
1	Unique name	Number of different dates	AliGW
2	Adam	1	3	Daniel: =SUM(IF(A2=@Sheet1!$A$2:$A$16,1/(COUNTIFS(Sheet1!$A$2:$A$16,A2,Sheet1!$B$2:$B$16,@Sheet1!$B$2:$B$16)),0))
3	Sam	0	3	AliGW: =SUM(IF(A2=Sheet1!$A$2:$A$16,1/(COUNTIFS(Sheet1!$A$2:$A$16,A2,Sheet1!$B$2:$B$16,Sheet1!$B$2:$B$16)),0))
4	Dan	0	2
5	John	0	1
6	Mike	0	1

Sheet: Sheet2

In French:

=SOMME(SI(A2=Sheet1!$A$2:$A$16;1/(NB.SI.ENS(Sheet1!$A$2:$A$16;A2;Sheet1!$B$2:$B$16;Sheet1!$B$2:$B$16));0))

**danielfr** · 11-28-2021, 07:48 AM

Ok. Thank you. I will try to figure out what's wrong. And thank you for your time

**AliGW** · 11-28-2021, 07:49 AM

No worries - let us know how you get on.

**danielfr** · 11-28-2021, 08:42 AM

It works now with the sample dates file. I tried in my main file and after entering the formula ( and of course having changed the sheet names and the cells ), Excel asks me to update the values from a sheet. I didn't have this message with the "dates" file;

**AliGW** · 11-28-2021, 09:13 AM

In that case someting is NOT right with your sheet names. Check these carefully - Excel doesn't recognise at least one of them and is telling you to open the workbook where it is found. Make sure it is spelt correctly. I would set ranges by selecting them rather than by changing them manually, which is prone to user error.

**danielfr** · 11-28-2021, 01:58 PM

It works now with my main file. Thanks so much for your help.
May I ask you another help. I have written a complicated and slow macro to find out for every name, his last date . I think that this could also be resolved with a formula
See attached file ( sheet2, column"C")
Thanks in advance for your help.

**danielfr** · 11-28-2021, 04:23 PM

I found it:

=MAX(IF(Sheet1!$A$2:$A$20=A3;Sheet1!$B$2:$B$20)) with CSE

**AliGW** · 11-28-2021, 05:18 PM

In future, please start a new thread fir each separate query.

Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Re: Number of different dates based on multiple criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Count the number of times a data appear based on multiple criteria between 2 dates

[SOLVED] Count the number of times a data appear based on multiple criteria between 2 dates

Bookmarks

Bookmarks

Posting Permissions