Select Distinct in Excel

[welshgasman]

Hi all,
I have an excel workbook, that I use to keep track of all my trips as a community driver.
I would like to see a count of the number of days I volunteered per weekday, Mon, Tue etc.
My sheet is one row per trip with the date, so if I had 4 trips that day there would be 4 rows/records with that date, which let us say is. Monday.
Now I can count the trips on each weekday easy enough with a CountIf(), but I just want to know how many Mondays thru Sunday I volunteered, so for the above example that would be 1 Monday.
If this was Access I could use Select Distinct for date to get this data. Is there a similar method in Excel?

TIA

 

[jasonb75]

Something like this maybe? I got the impression from your post that you might have the dates and weekdays listed out in a similar way to the mini sheet below. If you don't have the weekdays listed then that's fine, we should be able to do something without once we know what you do have in your sheet.

Book1
	A	B
2	02/10/2023	Mon
3	03/10/2023	Tue
4	03/10/2023	Tue
5	04/10/2023	Wed
6	10/10/2023	Tue
7	04/10/2023	Wed
8
9
10
11	Mon	1
12	Tue	2
13	Wed	1
14	Thu	0
15	Fri	0
16	Sat	0
17	Sun	0
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=TEXT(A2,"ddd")
B11:B17	B11	=SUM(IFERROR(1/COUNTIFS($A$2:$A$7,$A$2:$A$7,$B$2:$B$7,$B$2:$B$7,$B$2:$B$7,A11),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[welshgasman]

Thank you jasonb75,
After googling some more, I found the sum(1/countif()) option, but cannot get it to work.
According to the link the range is repeated, but that gets me the same number for each workday, which I know is incorrect.
About to go out on a trip, but I have added a column for now in Q that gives me the weekday and the 7 weekdays in S and that formula in U

And the formula I am trying in the last column is
=SUMPRODUCT(1/COUNTIF(Q$4:Q$1220,Q$4:Q$1220))
or
=SUM(1/COUNTIF(Q$4:Q$1220,Q$4:Q$1220))
from

How to Count Unique Values in Excel? 3 Easy Ways

In this article, learn how to count unique values in Excel. Also, learn how to count distinct values in excel using 3 easy methods.

www.simonsezit.com

T

However I will take a look at that and see if I can understand and adapt to my situation.

 

[jasonb75]

According to the link the range is repeated, but that gets me the same number for each workday, which I know is incorrect.

Countif would only give you the number of unique records in the whole range, you need to use countifs to get the count with additional criteria.
You need to repeat all ranges that are relevant, then add the criteria as an additional range and parameter, with your example image it would be something like

=SUMPRODUCT(IFERROR(1/COUNTIFS(N$4:N$1220,N$4:N$1220,O$4:O$1220,O$4:O$1220,P$4:P$1220,P$4:P$1220,Q$4:Q$1220,Q$4:Q$1220,Q$4:Q$1220,S4),0))

Do you have a column with the actual dates in, or are you working solely with columns N:Q as shown in the image (the formula above assumes days in column N).

Also noting the filter at the top of your image, does the formula need to take that into consideration so that only visible records are counted (that is going to be significantly more complicated but should still be possible).

Final question (for now) are you still using excel 2007 as shown on your profile? If you have changed to a newer version then that may have extra functions that will make things easier if the filter needs to be considered.

 

[welshgasman]

@jasonb75
That has done the trick.

Thank you very much.

 

[welshgasman]

Countif would only give you the number of unique records in the whole range, you need to use countifs to get the count with additional criteria.
You need to repeat all ranges that are relevant, then add the criteria as an additional range and parameter, with your example image it would be something like

=SUMPRODUCT(IFERROR(1/COUNTIFS(N$4:N$1220,N$4:N$1220,O$4:O$1220,O$4:O$1220,P$4:P$1220,P$4:P$1220,Q$4:Q$1220,Q$4:Q$1220,Q$4:Q$1220,S4),0))

Do you have a column with the actual dates in, or are you working solely with columns N:Q as shown in the image (the formula above assumes days in column N).

Also noting the filter at the top of your image, does the formula need to take that into consideration so that only visible records are counted (that is going to be significantly more complicated but should still be possible).

Final question (for now) are you still using excel 2007 as shown on your profile? If you have changed to a newer version then that may have extra functions that will make things easier if the filter needs to be considered.

I have tested for Sat and Sun, the least number to check and they work fine, so I am assuming the otehrs are correct.
I might just link it ot Access and run a query to be doubly sure, but it all looks good.

Yes my column A is the date. I added the weekday column today and the weekdays column today.
Yes, still using 2007, it is enough for my needs. I am retired and just find the odd use for it, like my trip sheets and master sheet. This sheet is my master sheet, where the monthly data is appended to.
For my personal use only, not the community car use. They are just interested in the monthly sheets at the end of every month.

Thanks again.

 

[welshgasman]

Can confirm all is good. Values shown, are the same as the result Access queries.

 

[jasonb75]

Yes my column A is the date. I added the weekday column today and the weekdays column today.

With that in mind, this should give you the same results

=SUMPRODUCT(IFERROR(1/COUNTIFS(A$4:A$1220,$A4:A$1220,Q$4:Q$1220,Q$4:Q$1220,Q$4:Q$1220,S4),0))

Alternatively, I should be able to do it just from column A using other functions to eliminate the need for the additional columns if you would prefer to have a little less clutter in your sheet.