May 05 2022 04:14 PM
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)) |
May 05 2022 04:47 PM - edited May 05 2022 04:48 PM
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
May 05 2022 05:52 PM
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.