CODEWITHSUNDEEP
excelpivotpivot-tablepivot-chart
Mark
Mark

Reputation: 41

Excel Pivot table count between

I have a problem creating a pivot table/chart with between date function. I would like to know the total count of projects that are active between two dates. A Chart with on the x-axis week 1 - week 52 is what i'm trying to create.

Example Chart

20
10
5
0

week 1 week 2 week 3 .....

My columns:

Project ID | START WEEK NUMBER| END WEEK NUMBER

What have I already done?

Adding helper columns 1 till 52 with the following function:
=IF(AH$1=MEDIAN($AB10;$AD10);1;IF(AH$1=$AB10;1;IF(AH$1=$AD10;1;"")))

This will show a 1 when the column week header is equal or between date start week and end week. But with this I am still not able to create a chart with the weeks on the x-axis and the total count of that week.

Upvotes: 1

Views: 1723

Answers (2)

T.Hannah
T.Hannah

Reputation: 111

If you need to use a pivot table, try rearranging your source data as shown:

enter image description here

Upvotes: 0

T.Hannah
T.Hannah

Reputation: 111

Make a new sheet and in Column A put your weeks 1-52 down. In column B use this formula:

=COUNTIFS(Sheet6!$B$2:$B$18,"<="&A2,Sheet6!$C$2:$C$18,">="&A2)
  1. Sheet6! Needs to be replaced with the name of your Sheet, e.g. Projects!
  2. Column B range needs to be the entire range of your start week
  3. Column C range needs to be the entire range of your end week
  4. B and C dimensions need to match.

Upvotes: 0

Related Questions