In this article, we will learn how to count new customers per month in excel. In other words, we will need to count only the first appearance of a user in a particular month.
How to solve the problem ?
For this article we will be required to use the COUNTIFS function and EOMONTH function. Here we are given some values in a range and specific text value as criteria. We need to count the values where the formula includes all the values which ends with the given text or pattern
occurrence: value must match 1.
1: value to match occurrence
d_range : date values as range
date: date value as month criteria
Here we have the ID records and we need to find unique ID values.
I'm here to find the unique values. Some range references using the named range is given.
IDs named range are used for ID values.
dates named range use for date values.
Occurrence named range use for the occurrence count
Use this formula to calculate values in Jan month:
As you can see the total values which occur once in the Jan month comes out to be 6.
Now copy the formula in other cells using the drag down option or using the shortcut key Ctrl + D as shown below. And changing the month criteria to March month and Feb carefully.
As you can see all the different values using Excel formulas returns the count of IDs in the data table.
You can also get the count of repeated values in the data table using the below formula.
Use the formula
">1" criteria as count matches the occurrence more than one time.
Here are some observational notes shown below.
Hope this article about how to Count values which end with using wildcards in Excel is explanatory. Find more articles on COUNTIFS functions here.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at firstname.lastname@example.org
How to use the SUMPRODUCT function in Excel: Returns the SUM after multiplication of values in multiple arrays in excel.
COUNTIFS with Dynamic Criteria Range : Count cells dependent on other cell values in Excel.
COUNTIFS Two Criteria Match : Count cells matching two different criteria on list in excel.
COUNTIFS With OR For Multiple Criteria : Count cells having multiple criteria match using the OR function.
The COUNTIFS Function in Excel : Count cells dependent on other cell values.
How to Use Countif in VBA in Microsoft Excel : Count cells using Visual Basic for Applications code.
How to use wildcards in excel : Count cells matching phrases using the wildcards in excel
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.