CODEWITHSUNDEEP
exceluniquecountif
chesschi
chesschi

Reputation: 708

COUNTIFS and SUMPRRODUCT for unique text within a date range for all rows

I am trying to use COUNTIFS and SUMPRODUCT to count the number of unique text for a date within certain range for all rows.

For example, I would like to count the number of unique count for the items that are in February.

Date, Item Description, , Start, 2017-02-01
02/1/2017, abc,         , End,   2017-03-01
03/2/2017, def
05/2/2017, abc
08/2/2017, def
01/3/2017, abc
05/3/2017, def

If I specify the range like this (i.e. from row 1 to 6), it does work and return 2.

=SUMPRODUCT((($A1:$A6>=$E1)*($A1:$A6<$E2))/COUNTIFS($B1:$B6, $B1:$B6, $A1:$A6, ">="&$E1, $A1:$A6, "<"&$E2))

However, if I specify the whole column it won't work and return 0.

=SUMPRODUCT((($A:$A>=$E1)*($A:$A<$E2))/COUNTIFS($B:$B, $B:$B, $A:$A, ">="&$E1, $A:$A, "<"&$E2))

Please suggest how can I fix this to do for all rows?

Upvotes: 0

Views: 594

Answers (2)

QHarr
QHarr

Reputation: 84475

You could use a dynamic named range e.g. Dates and enter this into the formula. It will then only evaluate for the required number of rows. Won't have the added luxury of barry houdini's answer which caters for blank cells in the range.

Ctrl + F3 will open the Name Manager and there you can click > New and enter Dates for the name and use a variation on the following formula for the Refers to:

=OFFSET(Sheet1!$A$2,0,0,COUNT(Sheet1!$A$2:$A$1048576),1)

The $A$1048576 is the last row in versions of Excel above 2003 and $A$65536 would be for before.

=SUMPRODUCT(((Dates>=$E1)*(Dates<$E2))/COUNTIFS(OFFSET(Dates,,1,,1), OFFSET(Dates,,1,,1), Dates, ">="&$E1, Dates, "<"&$E2))

Upvotes: 1

barry houdini
barry houdini

Reputation: 46451

Probably more efficient to use an array formula with FREQUENCY, and that type of formula can cope with blank rows so you should just make the range large enough to cater for current data and possible future expansion, e.g. for data in rows 2 to 1000 you can use an array formula like this:

=SUM(IF(FREQUENCY(IF(A$2:A$1000>=E1,IF(A$2:A$1000<E2,MATCH(B$2:B$1000,B$2:B$1000,0))),ROW(B$2:B$1000)-ROW(B$2)+1),1))

confirm with CTRL+SHIFT+ENTER

Upvotes: 1

Related Questions