Sum-Unique - filter

T

tim_crouse

New Member
Joined
Dec 6, 2023
Messages
4
Office Version
  1. 2021
Platform
  1. Windows
I am trying to do the following on a summary tab:
1. Perform a summary calculation of common part numbers
2. Display the total qty as a unique row displaying the total qty of the duplicate part numbers into col "A" on the "SUMMARY" TAB
2A. If all the rows of duplicate PN qty are 0 then omit the row from the summary report altogether
2B. If any of the qty values of the duplicate PN is in error (#NAME, Blank, or something other than a number) go ahead and create that unique PN row in the SUMMARY Ta
but return "T.B.V." in the Qty Col "A", To Be Verified.

Basically, I am trying to create a summary report that reduces multiple rows with the same information down to a single row with a summary total. And provide some feedback if there is an error in the data set.

I have tried many variations of UNIQUE and FILTER. I can get the functions to behave individually but when I couple them together into a single formula I get a screen full of #NAME or a screen full of zeros ( I apply the Ctrl + Sht + Enter to apply an array operation on the formula )

=UNIQUE(FILTER(MEL!C$3:C$83, (MEL!A$3:A$83<>"")*(MEL!A$3:A$83<>0)))
=SUMIFS(MEL!A$2:A$83, MEL!C$2:C$83, UNIQUE(FILTER(MEL!C$2:C$83, MEL!A$3:A$83<>0)))


SUMMARY REPORT HELP REQUEST.xlsx
ABCDEFGH
1
2#REF!#N/A
3
4
5
6
7203
8
9#NAME?
10
11
12
13
140
15
16
SUMMARY
Cell Formulas
RangeFormula
A2A2=UNIQUE(FILTER('D:\__WORKING DATA\__Active Projects\Nellis TTF Facility - 365th ISR TIFF\MELs\JAN 2024\1 - JAN 30 2024 - MEL CREATOR r22\[Design_MEL_NELLIS_R99 r25.xlsm]MEL'!J$3:J$800, ('D:\__WORKING DATA\__Active Projects\Nellis TTF Facility - 365th ISR TIFF\MELs\JAN 2024\1 - JAN 30 2024 - MEL CREATOR r22\[Design_MEL_NELLIS_R99 r25.xlsm]MEL'!J$3:J$800<>"")*('D:\__WORKING DATA\__Active Projects\Nellis TTF Facility - 365th ISR TIFF\MELs\JAN 2024\1 - JAN 30 2024 - MEL CREATOR r22\[Design_MEL_NELLIS_R99 r25.xlsm]MEL'!J$3:J$800<>0)))
B2B2=UNIQUE(FILTER('D:\__WORKING DATA\__Active Projects\Nellis TTF Facility - 365th ISR TIFF\MELs\JAN 2024\1 - JAN 30 2024 - MEL CREATOR r22\[Design_MEL_NELLIS_R99 r25.xlsm]MEL'!H$3:Q$800, ('D:\__WORKING DATA\__Active Projects\Nellis TTF Facility - 365th ISR TIFF\MELs\JAN 2024\1 - JAN 30 2024 - MEL CREATOR r22\[Design_MEL_NELLIS_R99 r25.xlsm]MEL'!H$3:H$800<>0)*('D:\__WORKING DATA\__Active Projects\Nellis TTF Facility - 365th ISR TIFF\MELs\JAN 2024\1 - JAN 30 2024 - MEL CREATOR r22\[Design_MEL_NELLIS_R99 r25.xlsm]MEL'!J$3:J$800=INDEX($A$2:$A2, MATCH(0, COUNTIF($A$2:$A2, $A$2:$A2), 0)))))
A7A7=UNIQUE(MEL!A$3:E$83)
E9E9=UNIQUE(FILTER(MEL!C$3:C$83, (MEL!A$3:A$83<>"")*(MEL!A$3:A$83<>0)))
A14A14=SUMIFS(MEL!A$2:A$83, MEL!C$2:C$83, UNIQUE(FILTER(MEL!C$2:C$83, MEL!A$3:A$83<>0)))
Press CTRL+SHIFT+ENTER to enter array formulas.




SUMMARY REPORT HELP REQUEST.xlsx
ABCDE
1QTYMFGPART NUMBERPART DESCRIPTIONTRADE DESCRIPTION
2HD FLEX Enclosures
3203PANDUITFLEX1U061 RU HD FLEX ENCLOSURE (EDA USE)ENCLOSURE, HD FLEX 1RU
4128PANDUITFLEX2U062 RU HD FLEX ENCLOSURE (MDA-IC AND HDA USE)ENCLOSURE, HD FLEX 2RU
525NO 3RU HD FLEX ENC
657PANDUITFLEX4U064 RU HD FLEX ENCLOSURE (ZDA AND IC USE)ENCLOSURE, HD FLEX 4RU
74PANDUITFLEX1UPN061 RU HD FLEX ENCLOSURE (SINGLE ZONE ZB & ABV CLG ZB USE)ENCLOSURE, HD FLEX 1RU - HALF DEPTH
8203PANDUITFLEXPLATE1U1 RU PLATE SLACK MANAGER (USE FOR 1RU ENC)SLACK MANAGER PLATE, HD FLEX 1U
9128PANDUITFLEXPLATE2U2 RU PLATE SLACK MANAGER (USE FOR 2RU ENC)SLACK MANAGER PLATE, HD FLEX 1U
1057PANDUITFLEXPLATE4U4 RU PLATE SLACK MANAGER (USE FOR 4RU ENC)SLACK MANAGER PLATE, HD FLEX 4U
11LC Breakout Cassettes
12#NAME?PANDUITFHCSA-12C-RCBN MM LCBO CASSETTE, KEYED, GRN, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, GREEN
13#NAME?PANDUITFHCSA-12B-RBBN MM LCBO CASSETTE, KEYED, RED, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, RED
14#NAME?PANDUITFHCSA-12D-RDBN MM LCBO CASSETTE, KEYED, YEL, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, YELLOW
15#NAME?PANDUITFHCSA-12E-REBN MM LCBO CASSETTE, KEYED, ORN, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, ORANGE
16#NAME?PANDUITFHCSA-12G-RGBN MM LCBO CASSETTE, KEYED, VIO, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, VIOLET
17#NAME?PANDUITFHCSA-12F-RFBN MM LCBO CASSETTE, KEYED, BLU, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, BLUE
18#NAME?PANDUITFHCSA-12H-RHBN MM LCBO CASSETTE, KEYED, AQU, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, AQUA
19#NAME?PANDUITFHC9N-12C-RCAS SM LCBO CASSETTE, KEYED, GRN, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, GREEN
20#NAME?PANDUITFHC9N-12B-RBAS SM LCBO CASSETTE, KEYED, RED, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, RED
21#NAME?PANDUITFHC9N-12D-RDAS SM LCBO CASSETTE, KEYED, YEL, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, YELLOW
22#NAME?PANDUITFHC9N-12E-REAS SM LCBO CASSETTE, KEYED, ORN, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, ORANGE
23#NAME?PANDUITFHC9N-12G-RGAS SM LCBO CASSETTE, KEYED, VIO, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, VIOLET
24#NAME?PANDUITFHC9N-12F-RFAS SM LCBO CASSETTE, KEYED, BLU, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, BLUE
25#NAME?PANDUITFHC9N-12H-RHAS SM LCBO CASSETTE, KEYED, AQU, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, AQUA
260PANDUITFHC9N-12C-RCAF SM LCBO CASSETTE, KEYED, GRN, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, GREEN
270PANDUITFHC9N-12B-RBAF SM LCBO CASSETTE, KEYED, RED, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, RED
280PANDUITFHC9N-12D-RDAF SM LCBO CASSETTE, KEYED, YEL, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, YELLOW
290PANDUITFHC9N-12E-REAF SM LCBO CASSETTE, KEYED, ORN, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, ORANGE
300PANDUITFHC9N-12G-RGAF SM LCBO CASSETTE, KEYED, VIO, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, VIOLET
310PANDUITFHC9N-12F-RFAF SM LCBO CASSETTE, KEYED, BLU, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, BLUE
320PANDUITFHC9N-12H-RHAF SM LCBO CASSETTE, KEYED, AQU, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, AQUA
3324PANDUITFHCSA-12C-RCBN MM LCBO CASSETTE, KEYED, GRN, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, GREEN
3412PANDUITFHCSA-12B-RBBN MM LCBO CASSETTE, KEYED, RED, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, RED
358PANDUITFHCSA-12D-RDBN MM LCBO CASSETTE, KEYED, YEL, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, YELLOW
365PANDUITFHCSA-12E-REBN MM LCBO CASSETTE, KEYED, ORN, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, ORANGE
372PANDUITFHCSA-12G-RGBN MM LCBO CASSETTE, KEYED, VIO, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, VIOLET
3825PANDUITFHCSA-12F-RFBN MM LCBO CASSETTE, KEYED, BLU, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, BLUE
3967PANDUITFHCSA-12H-RHBN MM LCBO CASSETTE, KEYED, AQU, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, AQUA
409PANDUITFHC9N-12C-RCAS SM LCBO CASSETTE, KEYED, GRN, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, GREEN
410PANDUITFHC9N-12B-RBAS SM LCBO CASSETTE, KEYED, RED, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, RED
420PANDUITFHC9N-12D-RDAS SM LCBO CASSETTE, KEYED, YEL, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, YELLOW
4315PANDUITFHC9N-12E-REAS SM LCBO CASSETTE, KEYED, ORN, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, ORANGE
4490PANDUITFHC9N-12G-RGAS SM LCBO CASSETTE, KEYED, VIO, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, VIOLET
452PANDUITFHC9N-12F-RFAS SM LCBO CASSETTE, KEYED, BLU, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, BLUE
46#NAME?PANDUITFHC9N-12H-RHAS SM LCBO CASSETTE, KEYED, AQU, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, AQUA
470PANDUITFHC9N-12C-RCAF SM LCBO CASSETTE, KEYED, GRN, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, GREEN
480PANDUITFHC9N-12B-RBAF SM LCBO CASSETTE, KEYED, RED, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, RED
490PANDUITFHC9N-12D-RDAF SM LCBO CASSETTE, KEYED, YEL, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, YELLOW
500PANDUITFHC9N-12E-REAF SM LCBO CASSETTE, KEYED, ORN, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, ORANGE
510PANDUITFHC9N-12G-RGAF SM LCBO CASSETTE, KEYED, VIO, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, VIOLET
520PANDUITFHC9N-12F-RFAF SM LCBO CASSETTE, KEYED, BLU, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, BLUE
530PANDUITFHC9N-12H-RHAF SM LCBO CASSETTE, KEYED, AQU, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, AQUA
54LC Breakout Cassettes UNKEYED
556PANDUITFHCSA-12C-GRBN MM LCBO CASSETTE, UNKEYED, GRN, METH-BCASSETTE, LCBO, MM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-B, GREEN
560PANDUITFHCSA-12B-RDBN MM LCBO CASSETTE, UNKEYED, RED, METH-BCASSETTE, LCBO, MM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-B, RED
570PANDUITFHCSA-12D-YLBN MM LCBO CASSETTE, UNKEYED, YEL, METH-BCASSETTE, LCBO, MM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-B, YELLOW
580PANDUITFHCSA-12E-ORBN MM LCBO CASSETTE, UNKEYED, ORN, METH-BCASSETTE, LCBO, MM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-B, ORANGE
590PANDUITFHCSA-12G-VLBN MM LCBO CASSETTE, UNKEYED, VIO, METH-BCASSETTE, LCBO, MM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-B, VIOLET
600PANDUITFHCSA-12F-BUBN MM LCBO CASSETTE, UNKEYED, BLU, METH-BCASSETTE, LCBO, MM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-B, BLUE
610PANDUITFHCSA-12H-AQBN MM LCBO CASSETTE, UNKEYED, AQU, METH-BCASSETTE, LCBO, MM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-B, AQUA
622PANDUITFHC9N-12C-RCAS SM LCBO CASSETTE, UNKEYED, GRN, METH-ACASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A, GREEN
630PANDUITFHC9N-12B-RBAS SM LCBO CASSETTE, UNKEYED, RED, METH-ACASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A, RED
640PANDUITFHC9N-12D-RDAS SM LCBO CASSETTE, UNKEYED, YEL, METH-ACASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A, YELLOW
650PANDUITFHC9N-12E-REAS SM LCBO CASSETTE, UNKEYED, ORN, METH-ACASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A, ORANGE
660PANDUITFHC9N-12G-RGAS SM LCBO CASSETTE, UNKEYED, VIO, METH-ACASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A, VIOLET
670PANDUITFHC9N-12F-RFAS SM LCBO CASSETTE, UNKEYED, BLU, METH-ACASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A, BLUE
680PANDUITFHC9N-12H-RHAS SM LCBO CASSETTE, UNKEYED, AQU, METH-ACASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A, AQUA
690PANDUITFHC9N-12C-RCAF SM LCBO CASSETTE, UNKEYED, GRN, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A FLIPPED, GREEN
700PANDUITFHC9N-12B-RBAF SM LCBO CASSETTE, UNKEYED, RED, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A FLIPPED, RED
710PANDUITFHC9N-12D-RDAF SM LCBO CASSETTE, UNKEYED, YEL, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A FLIPPED, YELLOW
720PANDUITFHC9N-12E-REAF SM LCBO CASSETTE, UNKEYED, ORN, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A FLIPPED, ORANGE
730PANDUITFHC9N-12G-RGAF SM LCBO CASSETTE, UNKEYED, VIO, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A FLIPPED, VIOLET
740PANDUITFHC9N-12F-RFAF SM LCBO CASSETTE, UNKEYED, BLU, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A FLIPPED, BLUE
750PANDUITFHC9N-12H-RHAF SM LCBO CASSETTE, UNKEYED, AQU, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A FLIPPED, AQUA
76MPO Adaptor Cassettes
7758PANDUITFHMP-6-BCMPO-MPO, UNKYD, (6) PORT, B-POLARITY, 72 STRCASSETTE, MPO ADAPTOR, MM, HD FLEX, (6) 12 STR PORTS, METH-B, GREEN
7874PANDUITFHMP-6-BBMPO-MPO, UNKYD, (6) PORT, B-POLARITY, 72 STRCASSETTE, MPO ADAPTOR, MM, HD FLEX, (6) 12 STR PORTS, METH-B, RED
7984PANDUITFHMP-6-BDMPO-MPO, UNKYD, (6) PORT, B-POLARITY, 72 STRCASSETTE, MPO ADAPTOR, MM, HD FLEX, (6) 12 STR PORTS, METH-B, YELLOW
800PANDUITFHMP-6-BEMPO-MPO, UNKYD, (6) PORT, B-POLARITY, 72 STRCASSETTE, MPO ADAPTOR, MM, HD FLEX, (6) 12 STR PORTS, METH-B, ORANGE
810PANDUITFHMP-6-BGMPO-MPO, UNKYD, (6) PORT, B-POLARITY, 72 STRCASSETTE, MPO ADAPTOR, MM, HD FLEX, (6) 12 STR PORTS, METH-B, VIOLET
820PANDUITFHMP-6-BFMPO-MPO, UNKYD, (6) PORT, B-POLARITY, 72 STRCASSETTE, MPO ADAPTOR, MM, HD FLEX, (6) 12 STR PORTS, METH-B, BLUE
830PANDUITFHMP-6-BHMPO-MPO, UNKYD, (6) PORT, B-POLARITY, 72 STRCASSETTE, MPO ADAPTOR, MM, HD FLEX, (6) 12 STR PORTS, METH-B, AQUA
84
MEL
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Welcome to the MrExcel board!

We cannot see ant formulas in MEL but there are a lot of #NAME? errors in column A of that sheet. What is going on there?
 
Upvote 0
deleted. Sorry I missed part of your first post when reading. What i had here was inapplicable. my apologies.
 

Attachments

  • 1706791298375.png
    1706791298375.png
    24.7 KB · Views: 10
Upvote 0
tim_crouse said:
Is there any way to post a small spreadsheet?
Click to expand...
What is wrong with how you did it in post 1?
It seems that perhaps you simply did not tick 'Cell Formulas' when you did the MEL sheet?

1706791752791.png
 
Upvote 0
Here is a MINISHEET With all options checked:

FORMULAS I HAVE TRIED ( A FEW VARIATIONS):

SUMMARY REPORT HELP REQUEST.xlsx
ABCDEFGHI
1
2#REF!#N/A
3
4
5
6
7203
8
9#NAME?
10
11
12
13
140
15
SUMMARY
Cell Formulas
RangeFormula
A2A2=UNIQUE(FILTER('[Design_MEL_NELLIS_R99 r25.xlsm]MEL'!J$3:J$800, ('[Design_MEL_NELLIS_R99 r25.xlsm]MEL'!J$3:J$800<>"")*('[Design_MEL_NELLIS_R99 r25.xlsm]MEL'!J$3:J$800<>0)))
B2B2=UNIQUE(FILTER('[Design_MEL_NELLIS_R99 r25.xlsm]MEL'!H$3:Q$800, ('[Design_MEL_NELLIS_R99 r25.xlsm]MEL'!H$3:H$800<>0)*('[Design_MEL_NELLIS_R99 r25.xlsm]MEL'!J$3:J$800=INDEX($A$2:$A2, MATCH(0, COUNTIF($A$2:$A2, $A$2:$A2), 0)))))
A7A7=UNIQUE(MEL!A$3:E$83)
E9E9=UNIQUE(FILTER(MEL!C$3:C$83, (MEL!A$3:A$83<>"")*(MEL!A$3:A$83<>0)))
A14A14=SUMIFS(MEL!A$2:A$83, MEL!C$2:C$83, UNIQUE(FILTER(MEL!C$2:C$83, MEL!A$3:A$83<>0)))
Press CTRL+SHIFT+ENTER to enter array formulas.





DATA SOURCE

SUMMARY REPORT HELP REQUEST.xlsx
ABCDE
1QTYMFGPART NUMBERPART DESCRIPTIONTRADE DESCRIPTION
2HD FLEX Enclosures
3203PANDUITFLEX1U061 RU HD FLEX ENCLOSURE (EDA USE)ENCLOSURE, HD FLEX 1RU
4128PANDUITFLEX2U062 RU HD FLEX ENCLOSURE (MDA-IC AND HDA USE)ENCLOSURE, HD FLEX 2RU
525NO 3RU HD FLEX ENC
657PANDUITFLEX4U064 RU HD FLEX ENCLOSURE (ZDA AND IC USE)ENCLOSURE, HD FLEX 4RU
74PANDUITFLEX1UPN061 RU HD FLEX ENCLOSURE (SINGLE ZONE ZB & ABV CLG ZB USE)ENCLOSURE, HD FLEX 1RU - HALF DEPTH
8203PANDUITFLEXPLATE1U1 RU PLATE SLACK MANAGER (USE FOR 1RU ENC)SLACK MANAGER PLATE, HD FLEX 1U
9128PANDUITFLEXPLATE2U2 RU PLATE SLACK MANAGER (USE FOR 2RU ENC)SLACK MANAGER PLATE, HD FLEX 1U
1057PANDUITFLEXPLATE4U4 RU PLATE SLACK MANAGER (USE FOR 4RU ENC)SLACK MANAGER PLATE, HD FLEX 4U
11LC Breakout Cassettes
12#NAME?PANDUITFHCSA-12C-RCBN MM LCBO CASSETTE, KEYED, GRN, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, GREEN
13#NAME?PANDUITFHCSA-12B-RBBN MM LCBO CASSETTE, KEYED, RED, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, RED
14#NAME?PANDUITFHCSA-12D-RDBN MM LCBO CASSETTE, KEYED, YEL, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, YELLOW
15#NAME?PANDUITFHCSA-12E-REBN MM LCBO CASSETTE, KEYED, ORN, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, ORANGE
16#NAME?PANDUITFHCSA-12G-RGBN MM LCBO CASSETTE, KEYED, VIO, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, VIOLET
17#NAME?PANDUITFHCSA-12F-RFBN MM LCBO CASSETTE, KEYED, BLU, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, BLUE
18#NAME?PANDUITFHCSA-12H-RHBN MM LCBO CASSETTE, KEYED, AQU, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, AQUA
19#NAME?PANDUITFHC9N-12C-RCAS SM LCBO CASSETTE, KEYED, GRN, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, GREEN
20#NAME?PANDUITFHC9N-12B-RBAS SM LCBO CASSETTE, KEYED, RED, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, RED
21#NAME?PANDUITFHC9N-12D-RDAS SM LCBO CASSETTE, KEYED, YEL, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, YELLOW
22#NAME?PANDUITFHC9N-12E-REAS SM LCBO CASSETTE, KEYED, ORN, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, ORANGE
23#NAME?PANDUITFHC9N-12G-RGAS SM LCBO CASSETTE, KEYED, VIO, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, VIOLET
24#NAME?PANDUITFHC9N-12F-RFAS SM LCBO CASSETTE, KEYED, BLU, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, BLUE
25#NAME?PANDUITFHC9N-12H-RHAS SM LCBO CASSETTE, KEYED, AQU, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, AQUA
260PANDUITFHC9N-12C-RCAF SM LCBO CASSETTE, KEYED, GRN, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, GREEN
270PANDUITFHC9N-12B-RBAF SM LCBO CASSETTE, KEYED, RED, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, RED
280PANDUITFHC9N-12D-RDAF SM LCBO CASSETTE, KEYED, YEL, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, YELLOW
290PANDUITFHC9N-12E-REAF SM LCBO CASSETTE, KEYED, ORN, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, ORANGE
300PANDUITFHC9N-12G-RGAF SM LCBO CASSETTE, KEYED, VIO, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, VIOLET
310PANDUITFHC9N-12F-RFAF SM LCBO CASSETTE, KEYED, BLU, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, BLUE
320PANDUITFHC9N-12H-RHAF SM LCBO CASSETTE, KEYED, AQU, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, AQUA
3324PANDUITFHCSA-12C-RCBN MM LCBO CASSETTE, KEYED, GRN, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, GREEN
3412PANDUITFHCSA-12B-RBBN MM LCBO CASSETTE, KEYED, RED, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, RED
358PANDUITFHCSA-12D-RDBN MM LCBO CASSETTE, KEYED, YEL, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, YELLOW
365PANDUITFHCSA-12E-REBN MM LCBO CASSETTE, KEYED, ORN, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, ORANGE
372PANDUITFHCSA-12G-RGBN MM LCBO CASSETTE, KEYED, VIO, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, VIOLET
3825PANDUITFHCSA-12F-RFBN MM LCBO CASSETTE, KEYED, BLU, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, BLUE
3967PANDUITFHCSA-12H-RHBN MM LCBO CASSETTE, KEYED, AQU, METH-BCASSETTE, LCBO, MM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-B, AQUA
409PANDUITFHC9N-12C-RCAS SM LCBO CASSETTE, KEYED, GRN, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, GREEN
410PANDUITFHC9N-12B-RBAS SM LCBO CASSETTE, KEYED, RED, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, RED
420PANDUITFHC9N-12D-RDAS SM LCBO CASSETTE, KEYED, YEL, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, YELLOW
4315PANDUITFHC9N-12E-REAS SM LCBO CASSETTE, KEYED, ORN, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, ORANGE
4490PANDUITFHC9N-12G-RGAS SM LCBO CASSETTE, KEYED, VIO, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, VIOLET
452PANDUITFHC9N-12F-RFAS SM LCBO CASSETTE, KEYED, BLU, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, BLUE
46#NAME?PANDUITFHC9N-12H-RHAS SM LCBO CASSETTE, KEYED, AQU, METH-ACASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A, AQUA
470PANDUITFHC9N-12C-RCAF SM LCBO CASSETTE, KEYED, GRN, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, GREEN
480PANDUITFHC9N-12B-RBAF SM LCBO CASSETTE, KEYED, RED, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, RED
490PANDUITFHC9N-12D-RDAF SM LCBO CASSETTE, KEYED, YEL, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, YELLOW
500PANDUITFHC9N-12E-REAF SM LCBO CASSETTE, KEYED, ORN, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, ORANGE
510PANDUITFHC9N-12G-RGAF SM LCBO CASSETTE, KEYED, VIO, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, VIOLET
520PANDUITFHC9N-12F-RFAF SM LCBO CASSETTE, KEYED, BLU, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, BLUE
530PANDUITFHC9N-12H-RHAF SM LCBO CASSETTE, KEYED, AQU, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, KEYED, (6) 2 STR PORTS, METH-A FLIPPED, AQUA
54LC Breakout Cassettes UNKEYED
556PANDUITFHCSA-12C-GRBN MM LCBO CASSETTE, UNKEYED, GRN, METH-BCASSETTE, LCBO, MM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-B, GREEN
560PANDUITFHCSA-12B-RDBN MM LCBO CASSETTE, UNKEYED, RED, METH-BCASSETTE, LCBO, MM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-B, RED
570PANDUITFHCSA-12D-YLBN MM LCBO CASSETTE, UNKEYED, YEL, METH-BCASSETTE, LCBO, MM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-B, YELLOW
580PANDUITFHCSA-12E-ORBN MM LCBO CASSETTE, UNKEYED, ORN, METH-BCASSETTE, LCBO, MM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-B, ORANGE
590PANDUITFHCSA-12G-VLBN MM LCBO CASSETTE, UNKEYED, VIO, METH-BCASSETTE, LCBO, MM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-B, VIOLET
600PANDUITFHCSA-12F-BUBN MM LCBO CASSETTE, UNKEYED, BLU, METH-BCASSETTE, LCBO, MM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-B, BLUE
610PANDUITFHCSA-12H-AQBN MM LCBO CASSETTE, UNKEYED, AQU, METH-BCASSETTE, LCBO, MM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-B, AQUA
622PANDUITFHC9N-12C-RCAS SM LCBO CASSETTE, UNKEYED, GRN, METH-ACASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A, GREEN
630PANDUITFHC9N-12B-RBAS SM LCBO CASSETTE, UNKEYED, RED, METH-ACASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A, RED
640PANDUITFHC9N-12D-RDAS SM LCBO CASSETTE, UNKEYED, YEL, METH-ACASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A, YELLOW
650PANDUITFHC9N-12E-REAS SM LCBO CASSETTE, UNKEYED, ORN, METH-ACASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A, ORANGE
660PANDUITFHC9N-12G-RGAS SM LCBO CASSETTE, UNKEYED, VIO, METH-ACASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A, VIOLET
670PANDUITFHC9N-12F-RFAS SM LCBO CASSETTE, UNKEYED, BLU, METH-ACASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A, BLUE
680PANDUITFHC9N-12H-RHAS SM LCBO CASSETTE, UNKEYED, AQU, METH-ACASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A, AQUA
690PANDUITFHC9N-12C-RCAF SM LCBO CASSETTE, UNKEYED, GRN, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A FLIPPED, GREEN
700PANDUITFHC9N-12B-RBAF SM LCBO CASSETTE, UNKEYED, RED, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A FLIPPED, RED
710PANDUITFHC9N-12D-RDAF SM LCBO CASSETTE, UNKEYED, YEL, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A FLIPPED, YELLOW
720PANDUITFHC9N-12E-REAF SM LCBO CASSETTE, UNKEYED, ORN, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A FLIPPED, ORANGE
730PANDUITFHC9N-12G-RGAF SM LCBO CASSETTE, UNKEYED, VIO, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A FLIPPED, VIOLET
740PANDUITFHC9N-12F-RFAF SM LCBO CASSETTE, UNKEYED, BLU, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A FLIPPED, BLUE
750PANDUITFHC9N-12H-RHAF SM LCBO CASSETTE, UNKEYED, AQU, METH-A FLIPPEDCASSETTE, LCBO, SM, HD FLEX, UNKEYED, (6) 2 STR PORTS, METH-A FLIPPED, AQUA
76MPO Adaptor Cassettes
7758PANDUITFHMP-6-BCMPO-MPO, UNKYD, (6) PORT, B-POLARITY, 72 STRCASSETTE, MPO ADAPTOR, MM, HD FLEX, (6) 12 STR PORTS, METH-B, GREEN
7874PANDUITFHMP-6-BBMPO-MPO, UNKYD, (6) PORT, B-POLARITY, 72 STRCASSETTE, MPO ADAPTOR, MM, HD FLEX, (6) 12 STR PORTS, METH-B, RED
7984PANDUITFHMP-6-BDMPO-MPO, UNKYD, (6) PORT, B-POLARITY, 72 STRCASSETTE, MPO ADAPTOR, MM, HD FLEX, (6) 12 STR PORTS, METH-B, YELLOW
800PANDUITFHMP-6-BEMPO-MPO, UNKYD, (6) PORT, B-POLARITY, 72 STRCASSETTE, MPO ADAPTOR, MM, HD FLEX, (6) 12 STR PORTS, METH-B, ORANGE
810PANDUITFHMP-6-BGMPO-MPO, UNKYD, (6) PORT, B-POLARITY, 72 STRCASSETTE, MPO ADAPTOR, MM, HD FLEX, (6) 12 STR PORTS, METH-B, VIOLET
820PANDUITFHMP-6-BFMPO-MPO, UNKYD, (6) PORT, B-POLARITY, 72 STRCASSETTE, MPO ADAPTOR, MM, HD FLEX, (6) 12 STR PORTS, METH-B, BLUE
830PANDUITFHMP-6-BHMPO-MPO, UNKYD, (6) PORT, B-POLARITY, 72 STRCASSETTE, MPO ADAPTOR, MM, HD FLEX, (6) 12 STR PORTS, METH-B, AQUA
84
MEL
 
Upvote 0
Well, it seems there are no formulas in MEL, which brings me back to at least part of my earlier question:
Peter_SSs said:
... there are a lot of #NAME? errors in column A of that sheet. What is going on there?
Click to expand...
 
Upvote 0
The MEL is the Data Source. The data/errors were intentional to provide a data set for the filter on the SUMMARY Tab to Work With.

Part of the original request was to have the UNIQUE FILTER recognize there is an ERROR and generate a T.B.V. Cell Value on the Summary
Tab


The SUMMARY Tab has the variations of the UNIQUE FILTER arrangements I have tried ( at least some of them anyway, I have tried many variations).

Does that explanation help?
 
Upvote 0
All I can do is make a few comments about the attempted formulas in SUMMARY above

Formula in A2
This is referring to a different workbook. Both sheets in post 6 are in the same workbook. In any case the formula refers to column J of MEL, which was not included in the mini sheet so we cannot see that data.

Formula in B2
The formula refers to columns H & J of MEL, which was not included in the mini sheet so we cannot see that data.

Formula in A7
Could not possibly return the value shown based on the MEL sheet data shown in post

Formula in E9
This produces the #NAME? error because of those errors in column A of MEL that I asked about before. If you replace those error values with non-error values (even blanks) then that formula will return some results.

Formula in A14
This returns zero because your FILTER function in that formula returns an error. It returns an error because your two ranges in that FILTER function are different sizes. One is C$2:C$83 and the other is A$3:A$83
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,931
Messages
6,175,465
Members
452,646
Latest member
tudou

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