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)) |
