Greenbehindthecells
Board Regular
- Joined
- May 9, 2023
- Messages
- 50
- Office Version
- 365
- Platform
- 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.
Tab Data:
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 | |||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
| 2 | |||||||||||||||||||||||
| 3 | Wanderers LLC | 8/30/2023 | |||||||||||||||||||||
| 4 | |||||||||||||||||||||||
| 5 | |||||||||||||||||||||||
| 6 | Blue | Blue COUNT | Green | Green COUNT | Red | Red COUNT | Shoe | Shoe COUNT | Sock | Sock COUNT | Glove | Glove COUNT | Hat | Hat COUNT | Eng | Eng COUNT | Span | Span COUNT | Missing | Missing COUNT | |||
| 7 | 21202 | 2 | 19019 | 2 | 19971 | 1 | No | 21202 | 1 | 19971 | 1 | 08030 | 1 | 19971 | 1 | 21202 | 1 | 19019 | 1 | ||||
| 8 | 08030 | 6 | 21202 | 1 | 21202 | 1 | 21202 | 19019 | 1 | 08030 | 1 | ||||||||||||
| 9 | 08030 | 1 | 21202 | ||||||||||||||||||||
| 10 | |||||||||||||||||||||||
| 11 | |||||||||||||||||||||||
| 12 | |||||||||||||||||||||||
| 13 | |||||||||||||||||||||||
| 14 | |||||||||||||||||||||||
| 15 | |||||||||||||||||||||||
| 16 | |||||||||||||||||||||||
| 17 | Sort combo with main formula (E7 & count in column F)not sorting | 8030 | 19019 | 0 | |||||||||||||||||||
| 18 | 19019 | 2 | 19019 | Blue | |||||||||||||||||||
| 19 | 08030 | 6 | 8030 | Green | |||||||||||||||||||
| 20 | #REF! | Red | |||||||||||||||||||||
| 21 | #REF! | ||||||||||||||||||||||
| 22 | #REF! | ||||||||||||||||||||||
| 23 | #REF! | ||||||||||||||||||||||
| 24 | #REF! | ||||||||||||||||||||||
| 25 | #REF! | ||||||||||||||||||||||
| 26 | #REF! | ||||||||||||||||||||||
| 27 | #REF! | ||||||||||||||||||||||
| 28 | |||||||||||||||||||||||
D | |||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| R6,T6,P6,N6,L6,J6,H6,F6,D6,V6 | R6 | =CONCAT(Q6," COUNT") |
| C7:C16,G7:G16,E7:E16 | C7 | =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:K16 | I7 | =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:S16 | Q7 | =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)),"") |
| R7 | R7 | =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:V8 | V7 | =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))) |
| D7 | D7 | =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:D13 | D8 | =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:F8 | F7 | =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:F13 | F9 | =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:H8 | H7 | =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:H13 | H9 | =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))) |
| J7 | J7 | =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:J13 | J8 | =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))) |
| L7 | L7 | =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:L13 | L8 | =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))) |
| N7 | N7 | =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:N13 | N8 | =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))) |
| P7 | P7 | =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:P13 | P8 | =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:T15 | T7 | =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:R17 | O17 | =IFERROR(TRANSPOSE(UNIQUE(SORTBY(F18:F32,G18:G32,-1))),"") |
| F18:F27 | F18 | =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:I27 | I18 | =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:G19 | G18 | =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 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | AX | AY | AZ | BA | BB | BC | BD | BE | BF | BG | BH | BI | BJ | BK | |||
| 1 | M ID | M Last | M First | Misc | Misc 1 | Misc 2 | Misc 3 | Misc 4 | Misc 5 | Misc 6 | M Name | M Sub | M com | Misc 10 | Misc 11 | Misc 12 | Misc 13 | Misc 14 | Misc 15 | Misc 16 | Misc 17 | Misc 18 | Misc 19 | Misc 20 | Misc 21 | Misc 22 | Misc 23 | Misc 24 | Misc 25 | Misc 26 | Misc 27 | Misc 28 | Misc 29 | Misc 30 | Misc 31 | M Add | M City | M State | M County | M Zip | M Phone | M Phone 2 | M Email | Misc 40 | M RA | M Et | M Lan | G Name | G Id | T ID | Misc 47 | Misc 48 | G Pc | Misc 50 | Misc 51 | Misc 52 | Misc 53 | Misc 54 | Misc 55 | Misc 56 | Misc 57 | Misc 58 | Report Date | ||
| 2 | 111222333 | Gem | Gio | COL | 1 Broadway | Paterson | NJ | Passiac | 07513 | Blue | Shoe | Eng | Travelers LLC | 8/30/2023 | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 3 | 111222333 | Gem | Gio | KED | 1 Broadway | Paterson | NJ | Passiac | 07513 | Blue | Sock | Span | Travelers LLC | 8/30/2023 | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 4 | 111222222 | Fel | Fir | COL | 2 Broadway | Philladelphia | PA | Montgomery | 19019 | Green | Missing | Wanderers LLC | 8/30/2023 | ||||||||||||||||||||||||||||||||||||||||||||||||||||
| 5 | 222222221 | Den | Dio | COL | 3 Broadway | Camden | NJ | Trenton | 08030 | Green | Hat | Missing | Wanderers LLC | 8/30/2023 | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 6 | 122222222 | Cen | Cam | KED | 4 Broadway | Rehobath | DE | Clark | 19971 | Red | Glove | Eng | Wanderers LLC | 8/30/2023 | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 7 | 122222222 | Cen | Cam | COL | 4 Broadway | Rehobath | DE | Clark | 19971 | Red | Glove | Eng | Wanderers LLC | 8/30/2023 | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 8 | 333333333 | Ben | Bom | KED | 5 Broadway | Baltomore | MD | Farm | 21202 | Blue | Glove | Missing | Wanderers LLC | 8/30/2023 | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 9 | 333333333 | Ben | Bom | COL | 5 Broadway | Baltomore | MD | Farm | 21202 | Blue | Glove | Missing | Wanderers LLC | 8/30/2023 | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 10 | 333333333 | Ben | Bom | A1C | 5 Broadway | Baltomore | MD | Farm | 21202 | Blue | Glove | Missing | Wanderers LLC | 8/30/2023 | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 11 | 333111111 | Aen | Aom | COL | 6 Broadway | Baltomore | MD | Farm | 21202 | Red | Sock | Span | Wanderers LLC | 8/30/2023 | |||||||||||||||||||||||||||||||||||||||||||||||||||
Tab Data | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
