New Contributor

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

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/2022 2/1/2022 3/1/2022 4/1/2022 M 39 32 31 31 D 15 6 5 8 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/2022 2/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

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

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

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

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.