Spikenaylor
Board Regular
- Joined
- Apr 14, 2013
- Messages
- 116
- Office Version
- 365
- Platform
- Windows
I have in my table 50000 rows
The row Label Column is a pivottable operated by a slicer to filter to the choices made by the user
I want the 2015StillOpen column to show on the date shown in the 2015 column is the record still open on that date.
ie.
Row 1, No the record is not open, dont count it.
Row 2 Yes the record is open, count it.
Row 3 Yes the record is still open, count it.
This will aplly for the full 50000 rows.
to add spice, I want it to count if the record is open on that date and if the project compares with the values in the Row Label Column.
The row label column could have upto 15 options, if we want to count every record for every project open.
I have come up with a formula for each row, but it looks vast and when updating, takes ages to recalculate. Can anyone advise me if I can shrink this to an array formula or some other efficient method.
Basic formula for checking just one row Label value is
The row Label Column is a pivottable operated by a slicer to filter to the choices made by the user
I want the 2015StillOpen column to show on the date shown in the 2015 column is the record still open on that date.
ie.
Row 1, No the record is not open, dont count it.
Row 2 Yes the record is open, count it.
Row 3 Yes the record is still open, count it.
This will aplly for the full 50000 rows.
to add spice, I want it to count if the record is open on that date and if the project compares with the values in the Row Label Column.
The row label column could have upto 15 options, if we want to count every record for every project open.
I have come up with a formula for each row, but it looks vast and when updating, takes ages to recalculate. Can anyone advise me if I can shrink this to an array formula or some other efficient method.
Basic formula for checking just one row Label value is
Code:
[
=SUM(COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$3)+)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$3,$D:$D,""))
/CODE]
This checks date opened is greater than the 2015 year start, date opened is <=checkdate, Date Closed is > checkdate, Project = Row Label value, + same again but counting blanks.
This gives me the correct results, but I need the formula to check for various projects that may be filtered in the Row Label column, this then gives me this vast forumla below
[CODE][
=SUM(COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$3)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$4)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$5)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$6)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$7)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$8)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$9)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$10)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$11)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$12)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$13)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$14)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$15)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$16)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$17)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$18)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$19)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$20)+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$3,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$4,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$5,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$6,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$7,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$8,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$9,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$10,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$11,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$12,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$13,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$14,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$15,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$16,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$17,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$18,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$19,$D:$D,"")+COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$20,$D:$D,""))
/CODE]
There must be a more efficient method to acheive the same results.
Regards
Spikenaylor
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Project[/TD]
[TD]DateOpened[/TD]
[TD]DateClosed[/TD]
[TD][/TD]
[TD][/TD]
[TD]2015[/TD]
[TD]2015StillOpen[/TD]
[TD][/TD]
[TD]Row Label[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]VSCR[/TD]
[TD]02/01/2015[/TD]
[TD]02/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]01/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]Task[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Task[/TD]
[TD]03/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]02/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]Deviation[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Deviation[/TD]
[TD]03/01/2015[/TD]
[TD]06/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]03/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]31/12/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]