count unique values per month

**tosteven** · 08-08-2016, 10:01 AM

I have a SQL query table with order data, and I am trying to figure out how many of each type of order occurred each month. each order can have multiple rows. I am having difficulties getting it to count only unique orders.

Order# Date Type
1 6/1/16 OoC
1 6/1/16 OoC
2 5/16/16 HNA 213
5 4/13/16 HNA 456
3 6/12/16 ACC

How many OoC orders in June? the answer is 1.
How many HNA orders in April? The answer is 1.

the Attached Sample is a small amount of data compared to the actual table, everything is on a single sheet and only has a small amount of the 3 columns that are needed. The actual is a large table that will grow as data is added from the query.

**alansidman** · 08-08-2016, 10:15 AM

In I2, =COUNTIFS($C$2:$C$16,"HNA*",$B$2:$B$16,">3/31/2016",$B$2:$B$16,"<5/1/2016")
Change up the dates and criteria for each of the other cells using this as a model.

Here is a link on using Countifs. https://www.ablebits.com/office-addi...iple-criteria/

**tosteven** · 08-08-2016, 10:48 AM

Thanks Alan, But this ignores column A, where the order numbers are listed multiple times. I want to count each order number only 1 time. If I put your formula into J2, (HNA for May) and adjust the dates range, it gives a value of 5, but in column A they are all the same order number. so the correct value should be 1.

**JeteMc** · 08-08-2016, 12:24 PM

This solution employees a helper column (D), which may be hidden for aesthetic purposes, which has the formula: =COUNTIFS(A$2:A2,A2)
The array entered formula* that fills in the table (I2:K4) is:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Note: the months listed in I1:K1 are now dates and the cells are formatted "mmmm".
Let me know if you have any questions.

Edit: I noticed an error in the formula, however I am not sure if it relates to the reason that the formula didn't work on actual data as stated in post #5.

**tosteven** · 08-08-2016, 02:24 PM

Is there any way to avoid a helper column? The Actual is a much larger table derived from a SQL Query that refreshed and adds new rows.
Also, i couldn't get the Array formula to work when i tried to adjust it to fit the Actual. I tried a countifs, that seems to work.
This is the Countifs formula from the Actual, where the helper column is AP; -C:C is the date column, M:M is the Type Column, and AP:AP is the helper column.
=COUNTIFS(PSDATA!C:C,">=7/1/16",PSDATA!C:C,"<=7/31/16",PSDATA!M:M,"HNA*",PSDATA!AP:AP,"=1")

I suppose I could add an "ISblank" to the helper column formula, and just propagate it down a couple thousand rows...

**Tony Valko** · 08-08-2016, 03:55 PM

Try this...

Data Range

	E	F	G	H
1		April	May	June
2	HNA	0	1	1
3	ACC	1	2	2
4	OoC	0	0	1

This array formula** entered in F2:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH($E2,$C$2:$C$16)),IF(TEXT($B$2:$B$16,"mmmm")=F$1,MATCH($A$2:$A$16,$A$2:$A$16,0))),ROW(A$2:A$16)-ROW(A$2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to H2 then down to F4:H4.

**tosteven** · 08-09-2016, 10:40 AM

Both JeteMc and Tony Valco have valid solutions. I think I will work to get Tony's working in the Actual Spreadsheet since it doesn't require a 'helper' column. I intend to keep them both handy ans I will be able to use them in various spreadsheets I work with.
Thanks to you both.
Solved.

**oeldere** · 08-09-2016, 10:48 AM

d2 =if(countif($C$2:$C2,$C2)=1,countif($C$2:$C2,$C2),"") and drag down.

after that a pivot table.

**Tony Valko** · 08-09-2016, 10:52 AM

You're welcome. Thanks for the feedback!

**yassna** · 08-26-2022, 11:27 AM

Hi, can someone help me to count unique values/names per month? Thanks a lot :-)

**AliGW** · 08-26-2022, 11:28 AM

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

count unique values per month

LinkBack

Thread Tools

Display

count unique values per month

Re: count unique values per month

Re: count unique values per month

Re: count unique values per month

Re: count unique values per month

Re: count unique values per month

Re: count unique values per month

Re: count unique values per month

Re: count unique values per month

Re: count unique values per month

Re: count unique values per month

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Count of unique contractors that were paid during the month

[SOLVED] Unique Total Value Count per Unique Lookup Values

Count number of unique cells if month =

[SOLVED] Formula to Count Unique Customer by Month

Count unique for specific month

Count unique days within month

count all unique values (text) + Definition of UNIQUE :D

Bookmarks

Bookmarks

Posting Permissions