Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill

G

Greenbehindthecells

Board Regular
Joined
May 9, 2023
Messages
50
Office Version
  1. 365
Platform
  1. Windows
Good afternoon!

I am looking for guidance to have a single formula count if (yes, count if seems obvious) with multiple criteria, exclude duplicates from a second column, and sort by (I don't mean to keep writing functions, it just happens this way). This started with my need for a stacked bar chart which is why I would like to have a formula that doesn't require a helper table for the table I need for the chart. This table will list the Zip codes according to criteria/ spill to 10 cells for each category however, it needs to sort by largest count of zipcodes.

My data is in a fixed format (Tab Data sheet) that is updated monthly. D3 & G3 will have dropdowns for field selection. My two formulas do not work together with SORTBY.

Column where duplicates are Duplicates M ID (col A Tab Data).
Cell first criteria d$3$ "d" sheet
Cell 2nd criteria $g$3 "d" sheet
Cell 3rd criteria C7 "D" sheet
Cell 4th criteria C$6$ "d" sheet

Formula for zipcodes =IFERROR(INDEX(UNIQUE(SORTBY(FILTER(MonthlyData[M Zip],(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M RA]=E$6),"No"),ROWS(UNIQUE(FILTER(MonthlyData[M ID],(MonthlyData[M Zip]=E8)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M RA]=$E$6),""),FALSE))),FALSE,FALSE),SEQUENCE(10)),"")

Formula to count instances with no duplicates=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],E7)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M ID],(MonthlyData[M Zip]=E7)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M RA]=$E$6),""),FALSE)))


Any help would be appreciated. I hope you can help me try to figure out the best way to make this work without adding a helper table (that I currently have). Thank you for reviewing this. I hope you have a wonderful day.

REL.xlsx
BCDEFGHIJKLMNOPQRSTUV
2
3Wanderers LLC8/30/2023
4
5
6BlueBlue COUNTGreenGreen COUNTRedRed COUNTShoeShoe COUNTSockSock COUNTGloveGlove COUNTHatHat COUNTEngEng COUNTSpanSpan COUNTMissingMissing COUNT
7212022190192199711No 212021199711080301199711212021190191
8 080306212021  212021 21202190191080301
9       08030121202
10        
11       
12        
13        
14 
15 
16
17Sort combo with main formula (E7 & count in column F)not sorting8030190190
1819019219019Blue
190803068030Green
20#REF!Red
21#REF!
22#REF!
23#REF!
24#REF!
25#REF!
26#REF!
27#REF!
28
D
Cell Formulas
RangeFormula
R6,T6,P6,N6,L6,J6,H6,F6,D6,V6R6=CONCAT(Q6," COUNT")
C7:C16,G7:G16,E7:E16C7=IFERROR(INDEX(UNIQUE(FILTER(MonthlyData[M Zip],(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M RA]=C$6),"No"),FALSE,FALSE),SEQUENCE(10)),"")
I7:I16,O7:O16,M7:M16,K7:K16I7=IFERROR(INDEX(UNIQUE(FILTER(MonthlyData[M Zip],(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M Et]=I$6),"No"),FALSE,FALSE),SEQUENCE(10)),"")
Q7:Q16,U7:U16,S7:S16Q7=IFERROR(INDEX(UNIQUE(FILTER(MonthlyData[M Zip],(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M Lan]=Q$6),"No"),FALSE,FALSE),SEQUENCE(10)),"")
R7R7=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],Q7)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M ID],(MonthlyData[M Zip]=Q7)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M Et]=$Q$6),""),FALSE)))
V7:V8V7=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],U7)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M ID],(MonthlyData[M Zip]=U7)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M Et]=$U$6),""),FALSE)))
D7D7=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],C7)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M ID],(MonthlyData[M Zip]=C7)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M RA]=$C$6),""),FALSE)))
D8:D10,D12:D13D8=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],C8)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M Zip],(MonthlyData[M Zip]=C8)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M RA]=$C$6),""),FALSE)))
F7:F8F7=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],E7)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M ID],(MonthlyData[M Zip]=E7)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M RA]=$E$6),""),FALSE)))
F9:F13F9=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],E9)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M Zip],(MonthlyData[M Zip]=E9)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M RA]=$E$6),""),FALSE)))
H7:H8H7=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],G7)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M ID],(MonthlyData[M Zip]=G7)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M RA]=$G$6),""),FALSE)))
H9:H13H9=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],G9)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M Zip],(MonthlyData[M Zip]=G9)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M RA]=$G$6),""),FALSE)))
J7J7=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],I7)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M ID],(MonthlyData[M Zip]=I7)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M Et]=$I$6),""),FALSE)))
J8:J13J8=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],I8)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M Zip],(MonthlyData[M Zip]=I8)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M Et]=$I$6),""),FALSE)))
L7L7=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],K7)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M ID],(MonthlyData[M Zip]=K7)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M Et]=$K$6),""),FALSE)))
L8:L13L8=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],K8)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M Zip],(MonthlyData[M Zip]=K8)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M Et]=$K$6),""),FALSE)))
N7N7=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],M7)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M ID],(MonthlyData[M Zip]=M7)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M Et]=$M$6),""),FALSE)))
N8:N13N8=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],M8)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M Zip],(MonthlyData[M Zip]=M8)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M Et]=$M$6),""),FALSE)))
P7P7=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],O7)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M ID],(MonthlyData[M Zip]=O7)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M Et]=$O$6),""),FALSE)))
P8:P13P8=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],O8)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M Zip],(MonthlyData[M Zip]=O8)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M Et]=$O$6),""),FALSE)))
T7:T15T7=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],S7)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M ID],(MonthlyData[M Zip]=S7)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M Et]=$S$6),""),FALSE)))
O17:R17O17=IFERROR(TRANSPOSE(UNIQUE(SORTBY(F18:F32,G18:G32,-1))),"")
F18:F27F18=IFERROR(INDEX(UNIQUE(SORTBY(FILTER(MonthlyData[M Zip],(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M RA]=E$6),"No"),ROWS(UNIQUE(FILTER(MonthlyData[M ID],(MonthlyData[M Zip]=E8)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M RA]=$E$6),""),FALSE))),FALSE,FALSE),SEQUENCE(10)),"")
I18:I27I18=SORTBY(INDEX(UNIQUE(FILTER(MonthlyData[M Zip],(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M RA]=E$6),"No"),FALSE,FALSE),SEQUENCE(10)),ROWS(UNIQUE(FILTER(MonthlyData[M ID],(MonthlyData[M Zip]=E7)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M RA]=$E$6),""),FALSE)),1)
G18:G19G18=IF(COUNTIFS(MonthlyData[G Name],$D$3,MonthlyData[Report Date],$G$3,MonthlyData[M Zip],E7)=0,"",ROWS(UNIQUE(FILTER(MonthlyData[M ID],(MonthlyData[M Zip]=E7)*(MonthlyData[G Name]=$D$3)*(MonthlyData[Report Date]=$G$3)*(MonthlyData[M RA]=$E$6),""),FALSE)))
Dynamic array formulas.



Tab Data:
REL.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBK
1M IDM LastM First MiscMisc 1Misc 2Misc 3Misc 4Misc 5Misc 6M NameM SubM comMisc 10Misc 11Misc 12Misc 13Misc 14Misc 15Misc 16Misc 17Misc 18Misc 19Misc 20Misc 21Misc 22Misc 23Misc 24Misc 25Misc 26Misc 27Misc 28Misc 29Misc 30Misc 31M AddM CityM StateM CountyM ZipM PhoneM Phone 2M EmailMisc 40M RAM EtM LanG NameG IdT IDMisc 47Misc 48G PcMisc 50Misc 51Misc 52Misc 53Misc 54Misc 55Misc 56Misc 57Misc 58Report Date
2111222333GemGioCOL1 BroadwayPatersonNJPassiac07513BlueShoeEngTravelers LLC8/30/2023
3111222333GemGioKED1 BroadwayPatersonNJPassiac07513BlueSockSpanTravelers LLC8/30/2023
4111222222FelFirCOL2 BroadwayPhilladelphiaPAMontgomery19019GreenMissingWanderers LLC8/30/2023
5222222221DenDioCOL3 BroadwayCamdenNJTrenton08030GreenHatMissingWanderers LLC8/30/2023
6122222222CenCamKED4 BroadwayRehobathDEClark19971RedGloveEngWanderers LLC8/30/2023
7122222222CenCamCOL4 BroadwayRehobathDEClark19971RedGloveEngWanderers LLC8/30/2023
8333333333BenBomKED5 BroadwayBaltomoreMDFarm21202BlueGloveMissingWanderers LLC8/30/2023
9333333333BenBomCOL5 BroadwayBaltomoreMDFarm21202BlueGloveMissingWanderers LLC8/30/2023
10333333333BenBomA1C5 BroadwayBaltomoreMDFarm21202BlueGloveMissingWanderers LLC8/30/2023
11333111111AenAomCOL6 BroadwayBaltomoreMDFarm21202RedSockSpanWanderers LLC8/30/2023
Tab Data
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
You must log in or register to reply here.

Similar threads

J
  • Solved
adding cells based on day of the week
Replies
1
Views
80
Asbestos_Jen
Asbestos_Jen
S
  • Question
Excel VBA drop down populate cells
Replies
4
Views
83
Fluff
Fluff
J
  • Question
sumif function with delayed application
Replies
5
Views
120
jbrown021286
J
A
  • Question
Fetch heads up stats between 2 teams
Replies
2
Views
125
AYouQueTai
A
A
  • Solved
Countifs? Count events in a range if something doesn't happen in another range
Replies
2
Views
162
AYouQueTai
A

Forum statistics

Threads
1,215,069
Messages
6,122,956
Members
449,096
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top