SOLVED
Home

Counting unique entries

%3CLINGO-SUB%20id%3D%22lingo-sub-753497%22%20slang%3D%22en-US%22%3ECounting%20unique%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753497%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20the%20formula%26nbsp%3B%3DSUMPRODUCT((%24G%242%3A%24G%24200%26lt%3B%26gt%3B%22%22)%2FCOUNTIFS(%24D%242%3A%24D%24200%2C%24D%242%3A%24D%24200%26amp%3B%22%22))%20to%20compile%20a%20list%20of%20unique%20entries%20and%20this%20is%20working%20well.%20However%2C%20I%20now%20want%20to%20refine%20my%20selection%20to%20count%20unique%20entries%20occurring%20between%201%2F1%2F2018%20and%2031%2F12%2F2018%20but%20am%20struggling%20to%20create%20a%20working%20formula.%20Any%20suggestions%20would%20be%20most%20welcome.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3CP%3EJim%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-753497%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753645%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20unique%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753645%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F292929%22%20target%3D%22_blank%22%3E%40Iriwel%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3EYou%20didn't%20say%20where%20the%20dates%20were%20located%2C%20so%20I%20assumed%20column%20E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20a%20formula%20like%3A%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(IFERROR((%24G%242%3A%24G%24200%26lt%3B%26gt%3B%22%22)*(%24E%242%3A%24E%24200%26gt%3B%3D--%221%2F1%2F18%22)*(%24E%242%3A%24E%24200%26lt%3B%3D--%2212%2F31%2F18%22)%2F%3CBR%20%2F%3ECOUNTIFS(%24D%242%3A%24D%24200%2C%24D%242%3A%24D%24200%26amp%3B%22%22%2C%24E%242%3A%24E%24200%2C%22%26gt%3B%3D1%2F1%2F18%22%2C%20%24E%242%3A%24E%24200%2C%22%26lt%3B%3D12%2F31%2F18%22)%2C0))%3C%2FPRE%3E%0A%3CP%3EWhile%20you%20can%20get%20away%20with%20%22%26gt%3B%3D1%2F1%2F18%22%20in%20the%20COUNTIFS%2C%20you%20need%20to%20convert%20the%20date%20to%20a%20number%20in%20the%20SUMPRODUCT.%20I%20did%20so%20by%20prefixing%20it%20with%20two%20minus%20signs%20in%20a%20row.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753832%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20unique%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753832%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Brad%20%E2%80%A6%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F374823%22%20target%3D%22_blank%22%3E%40Brad_Yundt%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20for%20your%20reply%20which%20I%20have%20been%20experimenting%20with%20this%20morning%20but%2C%20unfortunately%2C%20the%20formula%20keeps%20returning%20the%20value%200%20(Zero)%20-%20which%20is%20incorrect.%20If%20you%20have%20any%20further%20suggestions%20I'd%20be%20most%20grateful.%3C%2FP%3E%3CP%3EFYI%20-%20the%20dates%20are%20all%20in%20column%20G.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJim%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753891%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20unique%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753891%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F292929%22%20target%3D%22_blank%22%3E%40Iriwel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJim%2C%20Brad%20is%20from%20United%20States%20where%20months%20are%20in%20the%20front%20of%20days.%20You%20may%20convert%20dates%20into%20your%20locale%20format%2C%20or%2C%20to%20be%20not%20dependent%20on%20locale%2C%20it%20is%20better%20to%20use%20DATE()%20instead%20of%20date%20constants%2C%20like%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(IFERROR((%24G%242%3A%24G%24200%26gt%3B%3DDATE(2018%2C1%2C1))*(%24G%242%3A%24G%24200%26lt%3B%3DDATE(2018%2C12%2C31))%2F%0ACOUNTIFS(%24D%242%3A%24D%24200%2C%24D%242%3A%24D%24200%26amp%3B%22%22%2C%24G%242%3A%24G%24200%2C%22%26gt%3B%3D%22%26amp%3BDATE(2018%2C1%2C1)%2C%20%24G%242%3A%24G%24200%2C%22%26lt%3B%3D%22%26amp%3BDATE(2018%2C12%2C31))%2C0))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753914%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20unique%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753914%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20Sergei%3A%20that%20worked%20a%20treat.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJim%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-754012%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20unique%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754012%22%20slang%3D%22en-US%22%3E%3CP%3EJim%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-756509%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20unique%20entries%20(again!!!)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-756509%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F292929%22%20target%3D%22_blank%22%3E%40Iriwel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%20...%3C%2FP%3E%3CP%3EI%20think%20I%20got%20ahead%20of%20myself%20on%20Sunday%20in%20my%20enthusiasm%20for%20the%20solution%20you%20were%20providing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20input%20your%20formula%20to%20my%20worksheet%2C%20it%20returned%20a%20value%20of%201%20(one)%20which%20at%20the%20time%20I%20assumed%20was%20correct.%20I've%20now%20noticed%2C%20however%2C%20that%20the%20actual%20value%20being%20returned%20is%200.500%20and%20when%20I%20highlight%20the%20formula%20and%20press%20F9%2C%20it%20shows%20a%20value%20of%208%20-%20which%20is%20correct%20after%20I%20did%20a%20manual%20count.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20anyway%20the%20formula%20can%20be%20tweaked%3F%20Would%20much%20appreciate%20your%20input%20please%20as%20I%20also%20want%20to%20apply%20the%20formula%20to%20other%20years.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EJim%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-756654%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20unique%20entries%20(again!!!)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-756654%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F292929%22%20target%3D%22_blank%22%3E%40Iriwel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Jim%2C%3C%2FP%3E%0A%3CP%3EUse%20any%20cells%20as%20parameters%20to%20define%20your%20dates%20range%2C%20as%20A1%20and%20A2%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20860px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123382iDC07C5BF9A2EAC9A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAnd%20enter%20the%20formula%20as%20array%20one%20(i.e.%20Ctrl%2BShift%2BEnter%20instead%20of%20Enter).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-756736%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20unique%20entries%20(again!!!)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-756736%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESergei%20%E2%80%A6%20now%20we're%20cooking.%20Actually%20it%20was%20my%20stupidity%20for%20forgetting%20to%20add%20the%20array%20braces.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20again%2C%20my%20warmest%20thanks%20for%20your%20help%20and%20patience.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJim%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-756805%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20unique%20entries%20(again!!!)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-756805%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F292929%22%20target%3D%22_blank%22%3E%40Iriwel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJim%2C%20and%20I%20forgot%20to%20mention.%20I'm%20now%20on%20the%20version%20with%20Dynamic%20Arrays%20which%20actually%20doesn't%20require%20array%20formulas.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

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

9 Replies

@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.

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

@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))

 

@Sergei Baklan 

 

Many thanks Sergei: that worked a treat.

 

Jim

Jim, you are welcome

@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

Solution

@Deleted 

Hi Jim,

Use any cells as parameters to define your dates range, as A1 and A2 here

image.png

And enter the formula as array one (i.e. Ctrl+Shift+Enter instead of Enter).

@Sergei Baklan 

 

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

@Deleted 

Jim, and I forgot to mention. I'm now on the version with Dynamic Arrays which actually doesn't require array formulas.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies