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

holdaway

New Member
Joined
Jul 12, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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?!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
ABCDEFGHIJKLMNOP
1M10010200000
21/1/20212/1/2021C00000000100
3X00000001000
41/1/20211/2/20211/3/20211/4/20211/5/20211/6/20211/7/20211/8/20211/9/20211/10/20211/11/2021
51/4/20211/20/20211/25/20211613 M
61/6/20211/18/20211/31/2021129 M
71/1/20211/30/20212921M
81/20/20211/29/202198 
91/6/20211/8/20211/9/202123 MXC
1000 
11
Sheet6
Cell Formulas
RangeFormula
F1:AK3F1=COUNTIF(OFFSET(F5,0,MATCH(F4#,F4#,0)-1,1000),E1:E3)
F4:AK4F4=SEQUENCE(,B2-A2+1,A2)
D5:D10D5=B5-A5
E5:E10E5=NETWORKDAYS(A5,B5)
F5:AK10F5=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.
 
Upvote 0
Solution
Apologies for the confusion - your response works fantastically well, thank you so much!!
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top