Array for counting total number of items in list, less duplicates, by month

[Absolution]

Hi,


I need help with an array.


Basically I receive a list of data as below, and need to manipulate the data to show number of times a product is listed, by month while ignoring any duplicates in the month.


Data Table:

Products| Month

Item A | March 2013

Item B | March 2013

Item A | March 2013

Item C | March 2013

Item A | March 2013

Item D | April 2013

Item D | April 2013

Item B | April 2013

Item A | March 2013


Summary Table:

Month # Unique Items

March 2013 | xxx

April 2013 | xxx


I can get ban array to work when just trying to find the total of items less any duplicates by using: {=SUM(1/CountIf(Col A,Col A))}


The issue is I can't seem to get the unique numbers by month.


If i add in a Count(If( formula it comes back with a #Value! error.


Any help getting it to work would be a lifesaver.


N.B.

The file i receive is locked down except the table summary page. I usually use a pivot and update manually but with 300+ items and data that goes back 4 years this takes awhile.


DB

 

[Luke M]

I'd recommend reading this thread:

http://chandoo.org/forums/topic/combine-countifs-and-unique-function


Shows how to get unique counts with a criteria, such as a specific month.

 

[Absolution]

Thanks Luke, I have already attempted that one before and I just get a "0" each time.


Although in the post you say that a possible reason the original could be failing is due to the size of array?


Would that be the same for this one which gets the #Value! error: =SUM(1/COUNTIF(IF(Month=L.Month,List,0),L.Month=Month))


L.Month and List are both xrows, whereas Month is a singal cell reference.

 

[Kevin@Radstock]

Hi Absolution


Assuming your data is in A1:B10 including the column headers. Then to retrieve the unique months, in say D2:

=IFERROR(INDEX($B$2:$B$10,MATCH(0,INDEX(COUNTIF($D$1:D1,$B$2:$B$10),0,0),0)),"")

And copy down.


Then in E2 to retrieve the number of the unique values per month:

=SUM(SIGN(FREQUENCY(IF($B$2:$B$10=$D2,MATCH($A$2:$A$10,$A$2:$A$10,0)),ROW($2:$10)-ROW($1:$1))))

To commit, CTRL + SHIFT + ENTER, and copy down


Kevin

 

[SirJB7]

Hi, Absolution!


I like Kevin@Radstock formulas, I'd suggest you to use them.


But if you experiment a speed issue regarding great amounts of data and array formulas, maybe you want to give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Array%20for%20counting%20total%20number%20of%20items%20in%20list%2C%20less%20duplicates%2C%20by%20month%20%28for%20Absolution%20at%20chandoo.org%29.xlsx


It's a non-array formula only solution with this structure, then you can hide helper columns. In your unprotected Summary sheet do this:

A2:B2 : =SI(Hoja1!A2="";"";Hoja1!A2) -----> in english: =IF(Sheet1!A2="","",Sheet1!A2)

C2 : =SI(A2="";"";B2&"_"&A2) -----> in english: =IF(A2="","",B2&"_"&A2)

D2 : =SI(A2="";"";CONTAR.SI(C$2:C2;C2)) -----> in english: =IF(A2="","",COUNTIF(C$2:C2,C2))

E2 : =SI(A2="";"";D2&"_"&CONTAR.SI(D$2:D2;1)) -----> in english: =IF(A2="","",D2&"_"&COUNTIF(D$2:D2,1))

F2 : =SI.ERROR(INDICE(B:B;COINCIDIR(1&"_"&FILA()-1;E:E;0));"") -----> in english: =IFERROR(INDEX(B:B,MATCH(1&"_"&ROW()-1,E:E,0)),"")

G2 : =SI.ERROR(INDICE(A:A;COINCIDIR(1&"_"&FILA()-1;E:E;0));"") -----> in english: =IFERROR(INDEX(A:A,MATCH(1&"_"&ROW()-1,E:E,0)),"")


And you'll get in columns F:G the desired output.


Regards!

 

[SirJB7]

Hi, Absolution!

Something looked strange at me when I saw the formulas and is this: Kevin@Radkstock wrote formulas for counting the number of items for each month, while I listed the items that were unique for each month. Just to don't confuse you.

Regards!

 

[Miller C]

I might add in further columns here...

Column C as the following:

=A2&B2

Column D as:

=if(isna(Vlookup(C2,C$1:C1,1,false)),1,0)

you should be able to copy this down, and the lookup range should always be all rows above.


Column D will then be a count excluding duplicates. Can you get your data from there?

 

[Absolution]

Thanks for all the solutions guys. Unfortuntely I am unable to add any helper cells / columns as the worksheet is locked down in a special format.


Working on it over the weekend I came up with the following solution:


rList = The range for the list numbers

rMonth = The range of Months next to the rList

Month = The lookup reference.


{=SUM(IFERROR(1/(COUNTIFS(rList,rList,rMonth,Month)*(rMonth=Month)*1),0))}


2 Other solutions were provided by colleagues:


{=SUM(((rMonth)=Month)*(MATCH(IF(rMonth=Month,rList),IF(rMonth=Month,rList),0)=(ROW(rList)-1))}


{=SUM(IF(FREQUENCY(IF(rMonth=Month,VALUE(rList)),VALUE(rList)),1))}


Hope this helps someone else with a similiar need, otherwise... I hope you enjoy dissecting them


Absol

 

[SirJB7]

Hi, Absolution!

Glad you solved it. Thanks for your feedback sharing your solution and for your kind words too. And welcome back whenever needed or wanted.

Regards!