Forum Discussion
Welcome To Excel Space!!
You Can Discuss Any Type Of Discussion. Here, Everyone Can Do Any Type Of Excel(or any microsoft app related) Discussions Here. Enjoy!!😊
Hi janitor_8
I am trying to do a calculation to say on how many days in a month a bar is open
see data, Bar1 was open 2 in Jan, 3 in Feb. Bar2 was 1 in Jan 2 in Feb
on excel 365
data example
| Jan | 03-Jan | Bar1 |
| Jan | 03-Jan | Bar1 |
| Jan | 03-Jan | Bar2 |
| Jan | 05-Jan | Bar1 |
| Feb | 02-Feb | Bar1 |
| Feb | 02-Feb | bar1 |
| Feb | 02-Feb | bar2 |
| Feb | 04-Feb | bar1 |
| Feb | 04-Feb | bar1 |
| Feb | 04-Feb | bar2 |
You can create a pivot table based on the data. Make sure to add the data to the Data Model when you create it.
Add Month to the Rows area, Bar to the Columns area and Date to the Values area.
Then change the summary function for the Values field to Distinct Count in the Value Field Settings.
See the attached demo workbook.
Hi,
Thanks, that works well.
It does mean I then had to do a further formula to look at the pivot table data and pull based on the month and the bar.
Is there a way to do it all in one formula?
And alternative:
