Sorting & Counting with multiple criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1471867%22%20slang%3D%22en-US%22%3ESorting%20%26amp%3B%20Counting%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1471867%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20following%20columns.%20The%20data%20I%20want%20to%20count%20are%20in%20PRI%20INS%20ID%20and%20CLAIM%20ID.%20Each%20column%20has%20duplicates%20in%20them.%20How%20do%20I%20get%20Excel%20to%20count%20the%20number%20of%20claims%20per%20insurance%20ID%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20if%20PRI%20INS%20ID%20is%2036066%20and%20there%20is%20a%20total%20of%2017%20claims%2C%20some%20with%20the%20same%20claim%20ID%20number%2C%20how%20can%20I%20make%20it%20count%20each%20claim%20number%20only%20once.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22888%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2268%22%3EDOS%3C%2FTD%3E%3CTD%20width%3D%22121%22%3ELAST%20NAME%3C%2FTD%3E%3CTD%20width%3D%2293%22%3EFIRST%20NAME%3C%2FTD%3E%3CTD%20width%3D%2242%22%3EPT%20ID%3C%2FTD%3E%3CTD%20width%3D%22145%22%3EPRIMARY%20INS%20NAME%3C%2FTD%3E%3CTD%20width%3D%2251%22%3EPRI%20INS%20ID%3C%2FTD%3E%3CTD%20width%3D%2229%22%3EMD%3C%2FTD%3E%3CTD%20width%3D%2263%22%3ECLAIM%20ID%3C%2FTD%3E%3CTD%20width%3D%2244%22%3ECPT%3C%2FTD%3E%3CTD%20width%3D%2251%22%3EPYMT%3C%2FTD%3E%3CTD%20width%3D%22181%22%3EINFO%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%20I%20have%20used%20the%20COUNTIF%2C%20which%20will%20count%20the%20number%20of%20PRI%20INS%20ID%20but%20I%20can't%20figure%20out%20how%20to%20have%20it%20count%20the%20claim%20ID%20only%20once%20for%20each%20claim%20ID%20number.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMonica%20Shook%3C%2FP%3E%3CP%3E757.366.0295%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1471867%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-1471954%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20%26amp%3B%20Counting%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1471954%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F702589%22%20target%3D%22_blank%22%3E%40Monica_Shook%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMonica%2C%20do%20you%20consider%20PivotTable%20as%20an%20option%3F%20Perhaps%20that%20will%20be%20the%20easiest%20way%20-%20create%20PivotTable%20on%20your%20range%2C%20creating%20it%20check%20Add%20data%20to%20data%20model%2C%20select%20Distinct%20Count%20in%20field%20settings%20for%20Claim%20ID.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1471976%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20%26amp%3B%20Counting%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1471976%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20your%20attached%20workbook%20and%20assuming%20you%20put%20your%20choice%20of%20%3CSTRONG%3EPRI%20INS%20ID%3C%2FSTRONG%3E%26nbsp%3Bin%20O2%2C%20then%2C%20in%20P2%3A%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E1)%20If%20you%20have%20access%20to%20the%20Office%20365%20functions%20UNIQUE%20and%20FILTER%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23000080%22%3E%3DCOUNT(UNIQUE(FILTER(H%242%3AH%241000%2CF%242%3AF%241000%3DO2)))%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20Otherwise%2C%20%3CSTRONG%3Earray%20formula%20(CRTL%2BSHIFT%2BENTER)%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3E%3DSUM(IF(FREQUENCY(IF(F%242%3AF%241000%3DO2%2CH%242%3AH%241000)%2CH%242%3AH%241000)%2C1))%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECopy%20down%20to%20give%20similar%20results%20for%26nbsp%3BPRI%20INS%20ID%20entries%20in%20O3%2C%20O4%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20one%20difference%20in%20results%20generated%20by%20these%20two%20set-ups%20is%20for%26nbsp%3BPRI%20INS%20ID%26nbsp%3B%3CSTRONG%3E127679%3C%2FSTRONG%3E.%20There%20is%20one%20blank%20entry%20in%20the%20CLAIM%20ID%20column%20for%20this%26nbsp%3BPRI%20INS%20ID%3A%20of%20the%20two%20solutions%20given%20above%2C%201)%20excludes%20this%20result%2C%202)%20includes%20it.%20If%202)%20is%20the%20only%20option%20for%20you%20and%20you%20don't%20wish%20blank%26nbsp%3BCLAIM%20IDs%20to%20be%20included%20then%20use%20instead%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3E%3DSUM(IF(FREQUENCY(IF(F%242%3AF%241000%3DO2%2CIF(H%242%3AH%241000%26lt%3B%26gt%3B%22%22%2CH%242%3AH%241000))%2CH%242%3AH%241000)%2C1))%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1472056%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20%26amp%3B%20Counting%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1472056%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F702589%22%20target%3D%22_blank%22%3E%40Monica_Shook%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20file%20with%20your%20requested%20result%2C%20to%20quickly%20solve%20your%20query%20you%20need%20the%20new%20Excel%20Dynamic%20Arrays%20feature.%20Which%20is%20only%20available%20for%20Office365%20%26amp%3B%20Excel%20Online%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20thing%20to%20note%20that%20there%20are%20not%2017%20records%20against%20the%26nbsp%3B%3CSPAN%3EPRI%20INS%20ID%20is%2036066%2C%20there%20are%2020%20records%20out%20of%20which%2010%26nbsp%3B%3C%2FSPAN%3EUnique.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-06-17_22-38-56.png%22%20style%3D%22width%3A%20640px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F199315iECCA30CCFE8BB134%2Fimage-dimensions%2F640x468%3Fv%3D1.0%22%20width%3D%22640%22%20height%3D%22468%22%20title%3D%222020-06-17_22-38-56.png%22%20alt%3D%222020-06-17_22-38-56.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3EIf%20something%20is%20not%20working%20as%20you%20mention%2C%20please%20attached%20file%20with%20your%20desired%20output%20manually%20entered.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have the following columns. The data I want to count are in PRI INS ID and CLAIM ID. Each column has duplicates in them. How do I get Excel to count the number of claims per insurance ID?

 

For example, if PRI INS ID is 36066 and there is a total of 17 claims, some with the same claim ID number, how can I make it count each claim number only once.

 

DOSLAST NAMEFIRST NAMEPT IDPRIMARY INS NAMEPRI INS IDMDCLAIM IDCPTPYMTINFO

 

Thanks in advance. I have used the COUNTIF, which will count the number of PRI INS ID but I can't figure out how to have it count the claim ID only once for each claim ID number. 

 

Monica Shook

757.366.0295

 

3 Replies
Highlighted

@Monica_Shook 

Monica, do you consider PivotTable as an option? Perhaps that will be the easiest way - create PivotTable on your range, creating it check Add data to data model, select Distinct Count in field settings for Claim ID.

 

Please check attached.

Highlighted

Hi,

 

Using your attached workbook and assuming you put your choice of PRI INS ID in O2, then, in P2:


1) If you have access to the Office 365 functions UNIQUE and FILTER:

 

=COUNT(UNIQUE(FILTER(H$2:H$1000,F$2:F$1000=O2)))

 

2) Otherwise, array formula (CRTL+SHIFT+ENTER):

 

=SUM(IF(FREQUENCY(IF(F$2:F$1000=O2,H$2:H$1000),H$2:H$1000),1))

 

Copy down to give similar results for PRI INS ID entries in O3, O4, etc.

 

The one difference in results generated by these two set-ups is for PRI INS ID 127679. There is one blank entry in the CLAIM ID column for this PRI INS ID: of the two solutions given above, 1) excludes this result, 2) includes it. If 2) is the only option for you and you don't wish blank CLAIM IDs to be included then use instead:

 

=SUM(IF(FREQUENCY(IF(F$2:F$1000=O2,IF(H$2:H$1000<>"",H$2:H$1000)),H$2:H$1000),1))

 

Regards

Highlighted

Hi @Monica_Shook,

 

Please see the attached file with your requested result, to quickly solve your query you need the new Excel Dynamic Arrays feature. Which is only available for Office365 & Excel Online

 

One thing to note that there are not 17 records against the PRI INS ID is 36066, there are 20 records out of which 10 Unique.

 

2020-06-17_22-38-56.png

If something is not working as you mention, please attached file with your desired output manually entered.

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more