Excel 2016 count unique values in a range based on more than one range

0
votes

I am attempting to count the number of unique occurrences of an order number between a given date range, which i have successfully achieved using the code below ;

Column A = Date

Column B = Order Number

Cell W3 = From Date

Cell X3 = To Date

{=SUMPRODUCT(IF((A$2:A$10000<=X3)*(A$2:A$10000>=W3),
1/COUNTIFS(A$2:A$10000, "<="&X3,A$2:A$10000, ">="&W3,
B$2:B$10000,B$2:B$10000), 0))}

My issue is that i want to add another crietria which is held in Column U, that value being 'F' for arguments sake. I have tried to break this formula into bite size chunks to understand exactly what it is doing, but it is beyond me.

enter image description here

As you can see from the screen shot, there are 7 unique order numbers between the date range shown in Cell W3 to Cell X3 the result showing in Cell Y3 as per my formula.

I wish to add another formula that counts the same dataset with any row that has the value of 'F' in column U the result would be 1 as per cell Z3 highlighted in red, which i have manually entered.

Can anyone advise please.

2
excel-formula
Please provide some sample data in Column A, B, U, and Cell W3, X3, and indicate the expected output and the logic behind. - Terry W
Thanks Terry, i have added a screen shot and explanation - Prospidnick

2 Answers

1
votes

Here is another approach using methods from this article: Count unique values among duplicates

Suppose you have the following named ranges:

  • DateList being data in your date column;
  • OrderList being data in your order number column;
  • Col_U being the column that holds your extra criteria;
  • FromD being the From Date;
  • ToD being the To Date.

Use the following formula to return the unique count of order number in the given date range and criteria F:

=SUMPRODUCT(--(FREQUENCY(OrderList*(DateList>=FromD)*(DateList<=ToD)*(Col_U="F"),OrderList*(DateList>=FromD)*(DateList<=ToD)*(Col_U="F"))>0))-1

Solution

The difficult part is to understand the methodology of finding the filtered range using this equation =OrderList*(DateList>=FromD)*(DateList<=ToD)*(Col_U="F").

then use FREQUENCY(filtered_range,filtered_range)>0 to find out how many order numbers have more than one appearance in the filtered range, and lastly use SUMPRODUCT to return the total count. The -1 at the end of my formula is to taken out the count of duplicated 0 returned in the filtered range.

Let me know if you have any questions. Cheers :)

2
votes

enter image description here

Formula in cell H3:

=SUMPRODUCT(IF((A$2:A$10000<=F3)*(A$2:A$10000>=E3)*(C$2:C$10000=G3),
1/COUNTIFS(A$2:A$10000,"<="&F3,A$2:A$10000,">="&E3,C$2:C$10000,"="&G3,
B$2:B$10000,B$2:B$10000), 0))