Formula for Unique Occurence using multiple columns

J2Reed

New Member
Joined
Nov 12, 2018
Messages
14
I am trying to achieved the desired results on E4. I'm looking for a formula that will count a unique occurrence specific to a date, ignoring duplicates and blanks.
In this case, 19NOV19 has 2 trucks. 20NOV19 has 3 trucks. 21NOV19 has 2 trucks.
EXCEL PIC.PNG
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,674
Hi,

In cell E4, you can test following:
VBA Code:
=SUMPRODUCT(((1/COUNTIF(B2:B5,B2:B5)*(A2:A5<>""))))+SUMPRODUCT(((1/COUNTIF(B8:B14,B8:B14)*(A8:A14<>""))))+SUMPRODUCT(((1/COUNTIF(B15:B20,B15:B20)*(A15:A20<>""))))
Hope this will help
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,316
Just for fun with Power Query

1DATETRUCKTrucksCount
219/11/2019FEDEXTRUCKS7
319/11/2019FEDEX
419/11/2019FEDEX
519/11/2019UPS
6
7
820/11/2019FEDEX
920/11/2019TRACY
1020/11/2019TRACY
1120/11/2019TRACY
1220/11/2019UPS
1320/11/2019UPS
1420/11/2019UPS
1521/11/2019FEDEX
1621/11/2019FEDEX
1721/11/2019FEDEX
1821/11/2019FEDEX
1921/11/2019TRACY
2021/11/2019TRACY


Code:
// Table10
let
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    Filter = Table.SelectRows(Table.TransformColumnTypes(Source,{{"DATE", type date}, {"TRUCK", type text}}), each ([DATE] <> null)),
    GrpTR = Table.Group(Filter, {"TRUCK"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}}),
    GrpTRS = Table.Group(Table.AddColumn(GrpTR, "Trucks", each "TRUCKS"), {"Trucks"}, {{"Count", each List.Sum([Count]), type number}})
in
    GrpTRS
 

J2Reed

New Member
Joined
Nov 12, 2018
Messages
14
Do I have to add a formula for each date? or can this be ongoing example: B2:B1000 A2:A1000, ignoring blanks.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,308
Do I have to add a formula for each date? or can this be ongoing example: B2:B1000 A2:A1000, ignoring blanks.
Try this array formula
=SUM(IF(FREQUENCY(IF(A2:A1000<>"",IF(B2:B1000<>"",MATCH(A2:A1000&B2:B1000,A2:A1000&B2:B1000,0))),ROW(A2:A1000)-ROW(A2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.
 

Forum statistics

Threads
1,078,526
Messages
5,340,988
Members
399,404
Latest member
bmm

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top