Spillception, aka spilling a count formula that refers to a spilled array

[holdaway]

I think either I'm overthinking things, or this simply isn't possible. Unfortunately the xl2bb addin doesn't seem to like my computer and crashes Excel when I try to run it, so I'll do my best to explain what I have and what I want.

Firstly, I have a sequence function that creates a list of dates between a defined start and end date.

• =SEQUENCE(1,(B5-A5+1),DATE(YEAR(A5),MONTH(A5),DAY(A5)))
  • Note: A5 = project start date, B5 = project end date.

Secondly, I have individual tasks that have their own start and end dates, with a spill array marks the start date as "M", the end date as "C" and the delayed end date as "X".

• =IF($A2=F$1#,"M",IF(ISBLANK($C2),IF($B2=F$1#,"C",""),IF($C2=F$1#,"C",IF($B2=F$1#,"X",""))))
  • Note: A2 is the task start date, B2 is the task end date, and C2 is the delayed end date.
  • I also have a count of days between start and finish (D2) and a count of weekdays (E2).
  • Rows are repeated in line with the number of tasks.

What I'd ideally like to include is a spilled count formula (effectively a total) that reviews this data and counts the number of M, X and C in each column, e.g. Counts column F, and automatically spills to the final column in line with the other spills formulae. Obviously a countif works great for a single column, but if it's possible, I'd like to spill the count formula so that it automatically populates the top of each column, for as many columns as there are days.

Is this possible?!

 

[Eric W]

Welcome to the forum!

I was a bit confused, since your start and end dates (A5, B5) seem to be in the range of your task dates. I rearranged a few things. See if this is the basic idea of what you want:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1					M	1	0	0	1	0	2	0	0	0	0	0
2	1/1/2021	2/1/2021			C	0	0	0	0	0	0	0	0	1	0	0
3					X	0	0	0	0	0	0	0	1	0	0	0
4						1/1/2021	1/2/2021	1/3/2021	1/4/2021	1/5/2021	1/6/2021	1/7/2021	1/8/2021	1/9/2021	1/10/2021	1/11/2021
5	1/4/2021	1/20/2021	1/25/2021	16	13				M
6	1/6/2021	1/18/2021	1/31/2021	12	9						M
7	1/1/2021	1/30/2021		29	21	M
8	1/20/2021	1/29/2021		9	8
9	1/6/2021	1/8/2021	1/9/2021	2	3						M		X	C
10				0	0
11
Sheet6

Cell Formulas
Range	Formula
F1:AK3	F1	=COUNTIF(OFFSET(F5,0,MATCH(F4#,F4#,0)-1,1000),E1:E3)
F4:AK4	F4	=SEQUENCE(,B2-A2+1,A2)
D5:D10	D5	=B5-A5
E5:E10	E5	=NETWORKDAYS(A5,B5)
F5:AK10	F5	=IF($A5=F$4#,"M",IF(ISBLANK($C5),IF($B5=F$4#,"C",""),IF($C5=F$4#,"C",IF($B5=F$4#,"X",""))))
Dynamic array formulas.

 

[holdaway]

Apologies for the confusion - your response works fantastically well, thank you so much!!

 

[Eric W]

Happy to help! Thanks for the feedback. ?