count atleast once

S

sohaila

New Member
Joined
Oct 25, 2015
Messages
31
Office Version
  1. 365
Platform
  1. Windows
Hello
I want to count customer who's order only once in particular year. In 2019 customer5 order in once so count 1 in 2020 customer2 and coustomer3 order in once so count 2 and so on.
thanks.
Sunday, December 01, 2019Customer1
Sunday, December 01, 2019Customer5
Tuesday, January 01, 2019Customer1
Sunday, March 01, 2020Customer1
Sunday, March 01, 2020Customer2
Sunday, March 01, 2020Customer1
Sunday, March 01, 2020Customer3
Saturday, January 25, 2021Customer4
Tuesday, February 25, 2021Customer6
#########################Customer4
Wednesday, April 01, 2021Customer3
Wednesday, April 01, 2021Customer3
Wednesday, April 01, 2021Customer4
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Try this
Book1
ABCD
2Sunday, December 01, 2019Customer14
3Sunday, December 01, 2019Customer5
4Tuesday, January 01, 2019Customer1
5Sunday, March 01, 2020Customer1
6Sunday, March 01, 2020Customer2
7Sunday, March 01, 2020Customer1
8Sunday, March 01, 2020Customer3
9Saturday, January 25, 2021Customer4
10Tuesday, February 25, 2021Customer6
11Tuesday, February 25, 2021Customer4
12Wednesday, April 01, 2021Customer3
13Wednesday, April 01, 2021Customer3
14Wednesday, April 01, 2021Customer4
Sheet1
Cell Formulas
RangeFormula
D2D2=ROWS(UNIQUE(CHOOSE({1,2},RIGHT(A2:A14,4),B2:B14),,1))
 
Upvote 0
Sorry i couldn't explain in proper way i want only unique customer in perticular year
like in 2019 customer1 is double but customer5 is only one in 2019 that's count 1 In 2020 customer2 and customer3 are unique that's count 2 in year 2021 only customer6 is unique that's count1
thnks
Result
Sunday, December 01, 2019Customer1
2019​
2020​
2021​
Sunday, December 01, 2019Customer5
1​
2​
1​
Tuesday, January 01, 2019Customer1
Sunday, March 01, 2020Customer1
Sunday, March 01, 2020Customer2
Sunday, March 01, 2020Customer1
Sunday, March 01, 2020Customer3
Monday, January 25, 2021Customer4
Thursday, February 25, 2021Customer6
Friday, January 01, 2021Customer4
Thursday, April 01, 2021Customer3
Thursday, April 01, 2021Customer3
Thursday, April 01, 2021Customer4
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also are your dates text, or actual dates formatted like that?
 
Upvote 0
Ok i have updated my profile i hope you understand my question.

thanks
Result unique count with years
Order DateCustomer
2019​
2020​
2021​
Sunday, December 01, 2019Customer1
1​
2​
1​
Sunday, December 01, 2019Customer5
Tuesday, January 01, 2019Customer1
Sunday, March 01, 2020Customer1
Sunday, March 01, 2020Customer2
Sunday, March 01, 2020Customer1
Sunday, March 01, 2020Customer3
Monday, January 25, 2021Customer4
Thursday, February 25, 2021Customer6
Friday, January 01, 2021Customer4
Thursday, April 01, 2021Customer3
Thursday, April 01, 2021Customer3
Thursday, April 01, 2021Customer4
 
Upvote 0
sohaila said:
i hope you understand my question.
Click to expand...
This still needs clarifying
Fluff said:
are your dates text, or actual dates formatted like that?
Click to expand...
A formula that works with text dates will not work with proper dates and a formula that works with proper dates will not work with text dates so we need to know which is correct.

The easiest way to check is to double click one of the date cells, if it remains the same when the cursor is flashing inside the cell then it is text, if it changes to a normal date in the format of 01/01/2021 then it is a proper date.
 
Upvote 0
Ok, how about
+Fluff 1.xlsm
ABCDE
1Result
201/12/2019Customer1201920202021
301/12/2019Customer5121
401/01/2019Customer1
501/03/2020Customer1
601/03/2020Customer2
701/03/2020Customer1
801/03/2020Customer3
925/02/2021Customer4
1025/02/2021Customer6
1101/01/2021Customer4
1201/04/2021Customer3
1301/04/2021Customer3
1401/04/2021Customer4
Search
Cell Formulas
RangeFormula
C3:E3C3=ROWS(UNIQUE(FILTER(CHOOSE({1,2},YEAR($A$2:$A$14),$B$2:$B$14),YEAR($A$2:$A$14)=C2),,1))
 
Upvote 0
Thank You very much i really appreciate you
thanks a lot
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

IIII
  • Solved
Find Saturdays that are within a set of dates
Replies
2
Views
870
IIII
IIII
J
  • Question
Count deadlines help
Replies
6
Views
471
juanam
J
N
  • Question
Extracting data from certains dates
Replies
5
Views
377
Fluff
Fluff
B
  • Solved
Get week numbers and start and end dates based on a Friday to Thursday
Replies
5
Views
841
StephenCrump
S
drom
  • Solved
yyyyww formula
Replies
5
Views
990
Tetra201
Tetra201

Forum statistics

Threads
1,215,575
Messages
6,125,616
Members
449,238
Latest member
wcbyers

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top