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))
Prevent repeat entries in SharePoint list
In a column on my SharePoint list, the entries must either be equal to a specific value, or unique from every item in the list. For example, when a user adds a value in this column, it must either be "****" or a four digit number that is unique from all other numbers in that column. A valid list would be: 0001 0002 **** 0004 **** 0005 I want to make sure that any values in this column are either exactly "****" or unique from any other number value, but I can not do this with the existing enforce unique values option as this would not allow for any repeats of "****". Is there a way to do this with Power Automate and have it present an error message for any number repeats?