Reputation: 708
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
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
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