Simplify my formula

[Spikenaylor]

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

[
=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]

 

[pgc01]

Hi

A smaller version of your formula:

=SUMPRODUCT(COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$D:$D,">"&G3,$B:$B,$T$3:T20))+SUMPRODUCT(COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$3:T20,$D:$D,"")

 

[pgc01]

... and smaller (not tested):

=SUMPRODUCT(COUNTIFS($C:$C,">="&$G$3,$C:$C,"<="&G3,$B:$B,$T$3:T20,$D:$D,CHOOSE({1,2},">"&G3,"")))

 

[Spikenaylor]

Great pgc01
works great.

My only problem now is having 365 rows of this formula now causes excel to autocalculate extremely slowly.

Regards
Spikenaylor

 

[pgc01]

Hi

One thing you can do is to have manual calculation for that worksheet.

This way you can easily change whatever values you want.

When you need the result you calculate the worksheet.