calculate amount of days by name

How about using the advanced filter on the data tab of the ribbon. Filter for unique items.

well I want the sums to be on a separate page. I've never used the advanced filter tab, so im not familiar with how it works.

I'm using two separate pages with multiple rows of data for each year. Not sure how to use the advanced filter, is there a formula instead?

I assume i just filter the date and day column and then calculate the amount of times each day appears in separate columns on a different page, but this would require me to create a duplicate column to filter and calculate from.

here is a short tutorial on filtering for unique values. Once you have filtered them to a new page you can then apply countif function.

https://support.office.com/en-us/art...1-8daaec1e83c2

i can't adjust the column of dates as they are linked to other data, i'd have to create two duplicate columns to filter from or copy both current columns in the advanced filter options to another location to the do the calculation; either way i have to add more columns to an already extensive workbook. That only clutters up my page even more, is it necessary?

Place the filtered data in a remote section of your worksheet. You do not need to adjust the current dataset. Once you have run the filter, you can hide the remote columns so it does not affect your presentation.

Alternatively, a VBA solution may be available. I am too tired to work on that tonight. Will check back later if someone else has not checked in on this.

Change the answer headers from Wednesdays and Saturdays to Wednesday and Saturday and then use this array formula in D2, copied across:

=SUM(IF(FREQUENCY(IF($B$2:$B$17=D$1,$A$2:$A$17),IF($B$2:$B$17=D$1,$A$2:$A$17))>0,1))

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

C1 =if(countif($A$2:$A2,$A2)>1,"",1) and drag down to count the unique values.

After that a pivot table (to analyze the data).

See the attached file.

Edit:

since there where forumissues I could not upload the file in the original post.

You're welcome. We appreciate the feedback!

Thanks for marking the question solved.

It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.