I would like help finding unique values in a range when values in another range fit a criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-3328971%22%20slang%3D%22en-US%22%3EI%20would%20like%20help%20finding%20unique%20values%20in%20a%20range%20when%20values%20in%20another%20range%20fit%20a%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3328971%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20the%20table%20at%20the%20bottom%20of%20this%20message%2C%20I%20placed%20the%20formulas%20that%20I%20used%20in%20EXCEL%20to%20create%20a%20chart%20that%20looks%20at%20the%20data%20on%20the%20worksheet%20%22USE%22%20and%20analyzes%20it%20based%20on%20matching%20criteria%20in%20the%20date%20column%20(E)%20that%20falls%20in%20a%20particular%20month%20and%20matching%20criteria%20on%20column%20M%20where%20the%20text%20string%20begins%20with%20the%20letter%20listed%20on%20the%20left.%26nbsp%3B%20The%20bottom%20row%20analyzes%20the%20data%20in%20column%20B%20and%20sums%20the%20values%20if%20the%20dates%20fall%20in%20the%20particular%20months.%26nbsp%3B%20The%20resulting%20table%20is%20as%20follows%3A%3C%2FP%3E%3CTABLE%20width%3D%22332%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2244.5469px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2286.9375px%22%3E%3CSTRONG%3E1%2F1%2F2022%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2286.9375px%22%3E%3CSTRONG%3E2%2F1%2F2022%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2295.5625px%22%3E%3CSTRONG%3E3%2F1%2F2022%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2286.9375px%22%3E%3CSTRONG%3E4%2F1%2F2022%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2244.5469px%22%3E%3CSTRONG%3EM%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2286.9375px%22%3E39%3C%2FTD%3E%3CTD%20width%3D%2286.9375px%22%3E32%3C%2FTD%3E%3CTD%20width%3D%2295.5625px%22%3E31%3C%2FTD%3E%3CTD%20width%3D%2286.9375px%22%3E31%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2244.5469px%22%3E%3CSTRONG%3ED%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2286.9375px%22%3E15%3C%2FTD%3E%3CTD%20width%3D%2286.9375px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2295.5625px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2286.9375px%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2244.5469px%22%3E%3CSTRONG%3ETotal%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2286.9375px%22%3E%2440%2C215.25%3C%2FTD%3E%3CTD%20width%3D%2286.9375px%22%3E%2415%2C605.00%3C%2FTD%3E%3CTD%20width%3D%2295.5625px%22%3E%24348%2C358.44%3C%2FTD%3E%3CTD%20width%3D%2286.9375px%22%3E%2421%2C535.00%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20also%20used%20the%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%7B%3DSUMPRODUCT(--(FREQUENCY(MATCH(USE!%24A10%3A%24A218%2CUSE!%24A10%3A%24A218%2C0)%2CROW(USE!%24A10%3A%24A218)-ROW(USE!%24A10)%2B1)%26gt%3B0))%7D%3C%2FP%3E%3CP%3Eto%20show%20the%20total%20number%20of%20unique%20values%20in%20column%20A%20of%20the%20USE%20worksheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20add%20a%20row%20to%20the%20chart%20so%20that%20in%20each%20column%2C%20this%20SUMPRODUCT%20formula%20is%20combined%20with%20whatever%20other%20formula%20script%20I%20would%20need%20to%20get%20the%20number%20of%20unique%20values%20in%20column%20A%20of%20the%20USE%20worksheet%20for%20rows%20where%20the%20dates%20in%20column%20E%20fall%20in%20each%20of%20the%20months.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%20write%20out%20the%20formula%20that%20I%20would%20need%3F%26nbsp%3B%20I%20would%20very%20much%20appreciate%20the%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22580px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2258.9688px%22%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22261.281px%22%3E%3CSTRONG%3E1%2F1%2F2022%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22261.281px%22%3E%3CSTRONG%3E2%2F1%2F2022%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9688px%22%3E%3CP%3E%3CSTRONG%3EM%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22261.281px%22%3E%3CP%3E%3DCOUNTIFS(USE!%24E%2410%3A%24E%24999%2C%22%26gt%3B%3D%3C%2FP%3E%3CP%3E%22%26amp%3BC%2415%2CUSE!%24E%2410%3A%24E%24999%2C%22%26lt%3B%3D%22%26amp%3BEOMONTH(C%2415%2C0)%2C%3C%2FP%3E%3CP%3EUSE!%24M%2410%3A%24M%24999%2C%24B16%26amp%3B%22*%22)%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22261.281px%22%3E%3CP%3E%3DCOUNTIFS(USE!%24E%2410%3A%24E%24999%2C%22%26gt%3B%3D%3C%2FP%3E%3CP%3E%22%26amp%3BD%2415%2CUSE!%24E%2410%3A%24E%24999%2C%22%26lt%3B%3D%3C%2FP%3E%3CP%3E%22%26amp%3BEOMONTH(D%2415%2C0)%2C%3C%2FP%3E%3CP%3EUSE!%24M%2410%3A%24M%24999%2C%24B16%26amp%3B%22*%22)%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9688px%22%3E%3CP%3E%3CSTRONG%3ED%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22261.281px%22%3E%3CP%3E%3DCOUNTIFS(USE!%24E%2410%3A%24E%24999%2C%22%26gt%3B%3D%3C%2FP%3E%3CP%3E%22%26amp%3BC%2415%2CUSE!%24E%2410%3A%24E%24999%2C%22%26lt%3B%3D%3C%2FP%3E%3CP%3E%22%26amp%3BEOMONTH(C%2415%2C0)%2C%3C%2FP%3E%3CP%3EUSE!%24M%2410%3A%24M%24999%2C%24B17%26amp%3B%22*%22)%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22261.281px%22%3E%3CP%3E%3DCOUNTIFS(USE!%24E%2410%3A%24E%24999%2C%22%26gt%3B%3D%3C%2FP%3E%3CP%3E%22%26amp%3BD%2415%2CUSE!%24E%2410%3A%24E%24999%2C%22%26lt%3B%3D%3C%2FP%3E%3CP%3E%22%26amp%3BEOMONTH(D%2415%2C0)%2C%3C%2FP%3E%3CP%3EUSE!%24M%2410%3A%24M%24999%2C%24B17%26amp%3B%22*%22)%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9688px%22%3E%3CP%3E%3CSTRONG%3ETOTAL%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22261.281px%22%3E%3CP%3E%3DSUMIFS(USE!%24C%2410%3A%24C%24999%2C%3C%2FP%3E%3CP%3EUSE!%24E%2410%3A%24E%24999%2C%22%26gt%3B%3D%3C%2FP%3E%3CP%3E%22%26amp%3BC%2415%2CUSE!%24E%2410%3A%24E%24999%2C%22%26lt%3B%3D%22%26amp%3BEOMONTH(C%2415%2C0))%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22261.281px%22%3E%3CP%3E%3DSUMIFS(USE!%24C%2410%3A%24C%24999%2C%3C%2FP%3E%3CP%3EUSE!%24E%2410%3A%24E%24999%2C%22%26gt%3B%3D%3C%2FP%3E%3CP%3E%22%26amp%3BD%2415%2CUSE!%24E%2410%3A%24E%24999%2C%22%26lt%3B%3D%22%26amp%3BEOMONTH(D%2415%2C0))%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3328971%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Emultiple%20criteria%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eunique%20values%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3329030%22%20slang%3D%22en-US%22%3ERe%3A%20I%20would%20like%20help%20finding%20unique%20values%20in%20a%20range%20when%20values%20in%20another%20range%20fit%20a%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3329030%22%20slang%3D%22en-US%22%3EYou've%20posted%20this%20in%20our%20Away%20From%20Keyboard%20space%2C%20which%20is%20not%20meant%20for%20technical%20questions.%20I'm%20moving%20this%20thread%20to%20the%20Excel%20community.%20%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%2C%20%3CBR%20%2F%3E%3CBR%20%2F%3EMeenah%20%3CBR%20%2F%3ECommunity%20Manager%20%7C%20Tech%20Community%20Admin%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3329234%22%20slang%3D%22en-US%22%3ERe%3A%20I%20would%20like%20help%20finding%20unique%20values%20in%20a%20range%20when%20values%20in%20another%20range%20fit%20a%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3329234%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F875458%22%20target%3D%22_blank%22%3E%40Meenah_Khosraw%3C%2FA%3E%26nbsp%3B!%26nbsp%3B%20I%20tried%20my%20best%20to%20understand%20how%20all%20of%20this%20works%2C%20but%20I%20must%20have%20missed%20something.%26nbsp%3B%20It%20is%20very%20frustrating.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

In the table at the bottom of this message, I placed the formulas that I used in EXCEL to create a chart that looks at the data on the worksheet "USE" and analyzes it based on matching criteria in the date column (E) that falls in a particular month and matching criteria on column M where the text string begins with the letter listed on the left.  The bottom row analyzes the data in column B and sums the values if the dates fall in the particular months.  The resulting table is as follows:

 1/1/20222/1/20223/1/20224/1/2022
M39323131
D15658
Total$40,215.25$15,605.00$348,358.44$21,535.00

 

I have also used the formula

 {=SUMPRODUCT(--(FREQUENCY(MATCH(USE!$A10:$A218,USE!$A10:$A218,0),ROW(USE!$A10:$A218)-ROW(USE!$A10)+1)>0))}

to show the total number of unique values in column A of the USE worksheet. 

 

I would like to add a row to the chart so that in each column, this SUMPRODUCT formula is combined with whatever other formula script I would need to get the number of unique values in column A of the USE worksheet for rows where the dates in column E fall in each of the months.

 

Can anyone help me write out the formula that I would need?  I would very much appreciate the help!

 

 1/1/20222/1/2022

M

=COUNTIFS(USE!$E$10:$E$999,">=

"&C$15,USE!$E$10:$E$999,"<="&EOMONTH(C$15,0),

USE!$M$10:$M$999,$B16&"*")

=COUNTIFS(USE!$E$10:$E$999,">=

"&D$15,USE!$E$10:$E$999,"<=

"&EOMONTH(D$15,0),

USE!$M$10:$M$999,$B16&"*")

D

=COUNTIFS(USE!$E$10:$E$999,">=

"&C$15,USE!$E$10:$E$999,"<=

"&EOMONTH(C$15,0),

USE!$M$10:$M$999,$B17&"*")

=COUNTIFS(USE!$E$10:$E$999,">=

"&D$15,USE!$E$10:$E$999,"<=

"&EOMONTH(D$15,0),

USE!$M$10:$M$999,$B17&"*")

TOTAL

=SUMIFS(USE!$C$10:$C$999,

USE!$E$10:$E$999,">=

"&C$15,USE!$E$10:$E$999,"<="&EOMONTH(C$15,0))

=SUMIFS(USE!$C$10:$C$999,

USE!$E$10:$E$999,">=

"&D$15,USE!$E$10:$E$999,"<="&EOMONTH(D$15,0))

2 Replies

@Jeff-FreedomWorks

 

You've posted this in our Away From Keyboard space, which is not meant for technical questions. I'm moving this thread to the Excel community.

Thanks,

Meenah
Community Manager | Tech Community Admin

Thank you @Meenah_Khosraw !  I tried my best to understand how all of this works, but I must have missed something.  It is very frustrating.