Jul 13 2019 09:00 AM
Jul 13 2019 09:00 AM
I am using the formula =SUMPRODUCT(($G$2:$G$200<>"")/COUNTIFS($D$2:$D$200,$D$2:$D$200&"")) to compile a list of unique entries and this is working well. However, I now want to refine my selection to count unique entries occurring between 1/1/2018 and 31/12/2018 but am struggling to create a working formula. Any suggestions would be most welcome.
Thanks in advance
Jim
Jul 13 2019 01:17 PM
@Deleted,
You didn't say where the dates were located, so I assumed column E.
Try a formula like:
=SUMPRODUCT(IFERROR(($G$2:$G$200<>"")*($E$2:$E$200>=--"1/1/18")*($E$2:$E$200<=--"12/31/18")/
COUNTIFS($D$2:$D$200,$D$2:$D$200&"",$E$2:$E$200,">=1/1/18", $E$2:$E$200,"<=12/31/18"),0))
While you can get away with ">=1/1/18" in the COUNTIFS, you need to convert the date to a number in the SUMPRODUCT. I did so by prefixing it with two minus signs in a row.
Jul 14 2019 01:44 AM
Hi Brad …@Brad_Yundt
Many thanks for your reply which I have been experimenting with this morning but, unfortunately, the formula keeps returning the value 0 (Zero) - which is incorrect. If you have any further suggestions I'd be most grateful.
FYI - the dates are all in column G.
Jim
Jul 14 2019 04:48 AM
@Deleted
Jim, Brad is from United States where months are in the front of days. You may convert dates into your locale format, or, to be not dependent on locale, it is better to use DATE() instead of date constants, like
=SUMPRODUCT(IFERROR(($G$2:$G$200>=DATE(2018,1,1))*($G$2:$G$200<=DATE(2018,12,31))/ COUNTIFS($D$2:$D$200,$D$2:$D$200&"",$G$2:$G$200,">="&DATE(2018,1,1), $G$2:$G$200,"<="&DATE(2018,12,31)),0))
Jul 14 2019 06:45 AM
Jul 16 2019 03:29 AM
Jul 16 2019 03:29 AM
@Deleted
Hi Sergei ...
I think I got ahead of myself on Sunday in my enthusiasm for the solution you were providing.
When I input your formula to my worksheet, it returned a value of 1 (one) which at the time I assumed was correct. I've now noticed, however, that the actual value being returned is 0.500 and when I highlight the formula and press F9, it shows a value of 8 - which is correct after I did a manual count.
Is there anyway the formula can be tweaked? Would much appreciate your input please as I also want to apply the formula to other years.
Regards
Jim
Jul 16 2019 05:01 AM
Solution@Deleted
Hi Jim,
Use any cells as parameters to define your dates range, as A1 and A2 here
And enter the formula as array one (i.e. Ctrl+Shift+Enter instead of Enter).
Jul 16 2019 05:50 AM
Sergei … now we're cooking. Actually it was my stupidity for forgetting to add the array braces.
So again, my warmest thanks for your help and patience.
Jim
Jul 16 2019 06:20 AM
@Deleted
Jim, and I forgot to mention. I'm now on the version with Dynamic Arrays which actually doesn't require array formulas.
Jul 28 2020 05:13 PM
Jul 28 2020 07:03 PM
https://1drv.ms/x/s!Aml9MeEtEo3OhxbBPw-mWIc34YFS?e=jhtScc
The above file on my OneDrive shows several different formulas. Two require dynamic arrays for the UNIQUE and FILTER functions. Two do not.
I put month as text in column A, customer id in column B, Increase or Decrease in column C, and dates in column F. Cell F1 holds the text "March" and is used to build a date/time serial number.
=COUNTA(UNIQUE(FILTER(B2:B23,(A2:A23=F1)*(C2:C23="Decrease"))))
=COUNTA(UNIQUE(FILTER(B2:B23,(MONTH(D2:D23)=3)*(C2:C23="Decrease"))))
=SUMPRODUCT(IFERROR((A2:A23=F1)*(C2:C23="Decrease")/COUNTIFS(B2:B23,B2:B23,A2:A23,F1,C2:C23,"Decrease"),0))
=SUMPRODUCT(IFERROR((MONTH(D2:D23)=3)*(C2:C23="Decrease")/COUNTIFS(B2:B23,B2:B23,D2:D23,">=" & DATEVALUE(F1 & " 1, 2020"),D2:D23,"<=" & EOMONTH(DATEVALUE(F1 & " 1, 2020"),0),C2:C23,"Decrease"),0))
Jul 16 2019 05:01 AM
Solution@Deleted
Hi Jim,
Use any cells as parameters to define your dates range, as A1 and A2 here
And enter the formula as array one (i.e. Ctrl+Shift+Enter instead of Enter).