Cohort Waterfall by account count

[robbied13]

I am trying to build out a waterfall that will count how many customers we have based on what time they became a customer. The plan is to count the account IDs if they are new business and remove them if they have been cancelled. The issue is an account can have numerous opportunities marked as new business. As seen in the example of data provided on the left, account "1" has several new business opportunities but it should only be counted as 1 account. The table on the right is what I am trying to get to based on the data provided. Time is the x axis and the quarter the customer was new is the y axis. The values in the table are just hardcodes as I'm struggling with the equations. Any help would be much appreciated

---

[TABLE="width: 934"]
<colgroup><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD]Account ID (15)[/TD]
[TD]Type[/TD]
[TD]Date of New Business (Cohort)[/TD]
[TD]Cancellation Date[/TD]
[TD][/TD]
[TD][/TD]
[TD]Time[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]New Business[/TD]
[TD]Q1 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Q1 FY17[/TD]
[TD]Q2 FY17[/TD]
[TD]Q3 FY17[/TD]
[TD]Q4 FY17[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]New Business[/TD]
[TD]Q1 FY17[/TD]
[TD][/TD]
[TD]Cohort[/TD]
[TD]Q1 FY17[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]New Business[/TD]
[TD]Q1 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD]Q2 FY17[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]New Business[/TD]
[TD]Q1 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD]Q3 FY17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]New Business[/TD]
[TD]Q1 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD]Q4 FY17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]New Business[/TD]
[TD]Q2 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]New Business[/TD]
[TD]Q2 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]New Business[/TD]
[TD]Q2 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]New Business[/TD]
[TD]Q3 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]New Business[/TD]
[TD]Q4 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]New Business[/TD]
[TD]Q4 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]New Business[/TD]
[TD]Q4 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]New Business[/TD]
[TD]Q4 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]New Business[/TD]
[TD]Q4 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cancellation[/TD]
[TD]Q1 FY17[/TD]
[TD]Q2 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Cancellation[/TD]
[TD]Q1 FY17[/TD]
[TD]Q3 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cancellation[/TD]
[TD]Q1 FY17[/TD]
[TD]Q4 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Cancellation[/TD]
[TD]Q2 FY17[/TD]
[TD]Q4 FY17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl65 {font-family:Arial, sans-serif; mso-font-charset:0;}.xl66 {font-size:8.0pt; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; text-align:center; vertical-align:middle; mso-protection:unlocked visible;}.xl67 {background:#FFF2CC; mso-pattern:black none;}.xl68 {border-top:none; border-right:none; border-bottom:.5pt solid windowtext; border-left:none; background:#FFF2CC; mso-pattern:black none;}--></style>

 

[robbied13]

Where I'm at so far for the Q1 FY17...

First, I did the following formula to get unique ids for account numbers. It spit out 10, which is the total number of accounts in the sample
=SUMPRODUCT(1/COUNTIF(A2:A19,A2:A19))

Second, i just have a countifs formula to get the number of new business lines that were for Q1 FY17, This gives me 5.
=COUNTIFS(B2:B19,Sheet2!A1,C2:C19,F3)

What I'm trying to figure out is how to combine the formulas so that Q1 FY17 is 4.

 

[robbied13]

Combining Unique Values with multiple criteria array formula

I had posted elsewhere but heres a new thread just due to the amount of work I've put in.

I think I'm really close on this but could use some help. I'm trying to get a rolling count of all of our accounts. The table is what I'm trying to fill in and the detail is below. For some reason the array formula works in every cell except the Q4 FY17 row and I can't understand why. I've copied the formula in here. It basically say count all new business that came in within the fiscal quarter less any accounts that cancelled within the same quarter.

I posted the screen shot as well as the detail in text if either are helpful

{=SUM(IF(($F$1=$B$2:$B$20)*($C$2:$C$20<=I$2)*($D$2:$D$20>=$H$2)*($G3=$D$2:$D$20), 1/COUNTIFS($B$2:$B$20, $F$1, $A$2:$A$20, $A$2:$A$20, $C$2:$C$20, "<="&I$2, $C$2:$C$20, ">="&$H$2, $D$2:$D$20, $G3)), 0)-SUM(IF(($F$2=$B$2:$B$20)*($C$2:$C$20<=I$2)*($D$2:$D$20>=$H$2)*($G3=$D$2:$D$20), 1/COUNTIFS($B$2:$B$20, $F$2, $A$2:$A$20, $A$2:$A$20, $C$2:$C$20, "<="&I$2, $C$2:$C$20, ">="&$H$2, $D$2:$D$20, $G3)), 0)}

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl65 {color:blue; font-size:8.0pt; font-family:Arial, sans-serif; mso-font-charset:0; text-align:right; vertical-align:middle; mso-protection:unlocked visible;}.xl66 {font-family:Arial, sans-serif; mso-font-charset:0;}.xl67 {font-size:8.0pt; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; text-align:center; vertical-align:middle; mso-protection:unlocked visible;}.xl68 {background:#FFF2CC; mso-pattern:black none;}.xl69 {border-top:none; border-right:none; border-bottom:.5pt solid windowtext; border-left:none; background:#FFF2CC; mso-pattern:black none;}.xl70 {mso-number-format:"Short Date";}.xl71 {font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:"Short Date";}.xl72 {color:blue; font-size:8.0pt; font-family:Arial, sans-serif; mso-font-charset:0; text-align:right; vertical-align:middle; mso-protection:unlocked visible;}--></style>[TABLE="width: 609"]
<!--StartFragment--> <colgroup><col width="87" span="7" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 87"]New Business[/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="class: xl67, width: 87"]Time[/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD="class: xl72"]Cancellation[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]1/31/16[/TD]
[TD="class: xl70, align: right"]4/30/16[/TD]
[TD="class: xl71, align: right"]7/31/16[/TD]
[TD="class: xl71, align: right"]10/31/16[/TD]
[TD="class: xl71, align: right"]1/31/17[/TD]
[/TR]
[TR]
[TD="class: xl67"]Cohort[/TD]
[TD]Q1 FY17[/TD]
[TD="class: xl68, align: right"]0[/TD]
[TD="class: xl68, align: right"]4[/TD]
[TD="class: xl68, align: right"]3[/TD]
[TD="class: xl68, align: right"]2[/TD]
[TD="class: xl68, align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66"]Q2 FY17[/TD]
[TD="class: xl68, align: right"]0[/TD]
[TD="class: xl68, align: right"]0[/TD]
[TD="class: xl68, align: right"]2[/TD]
[TD="class: xl68, align: right"]2[/TD]
[TD="class: xl68, align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66"]Q3 FY17[/TD]
[TD="class: xl68, align: right"]0[/TD]
[TD="class: xl68, align: right"]0[/TD]
[TD="class: xl68, align: right"]0[/TD]
[TD="class: xl68, align: right"]1[/TD]
[TD="class: xl68, align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66"]Q4 FY17[/TD]
[TD="class: xl69, align: right"]0[/TD]
[TD="class: xl69, align: center"]#DIV/0![/TD]
[TD="class: xl69, align: center"]#DIV/0![/TD]
[TD="class: xl69, align: center"]#DIV/0![/TD]
[TD="class: xl69, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

[TABLE="width: 415"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Account ID (15)[/TD]
[TD]Type[/TD]
[TD]Date[/TD]
[TD]Date of New Business (Cohort)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]New Business[/TD]
[TD]2/1/16[/TD]
[TD]Q1 FY17[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]New Business[/TD]
[TD]2/1/16[/TD]
[TD]Q1 FY17[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]New Business[/TD]
[TD]3/1/16[/TD]
[TD]Q1 FY17[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]New Business[/TD]
[TD]3/1/16[/TD]
[TD]Q1 FY17[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]New Business[/TD]
[TD]4/1/16[/TD]
[TD]Q1 FY17[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]New Business[/TD]
[TD]5/1/16[/TD]
[TD]Q2 FY17[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]New Business[/TD]
[TD]5/1/16[/TD]
[TD]Q2 FY17[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]New Business[/TD]
[TD]6/1/16[/TD]
[TD]Q2 FY17[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]New Business[/TD]
[TD]8/1/16[/TD]
[TD]Q3 FY17[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]New Business[/TD]
[TD]11/1/16[/TD]
[TD]Q4 FY17[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]New Business[/TD]
[TD]11/1/16[/TD]
[TD]Q4 FY17[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]New Business[/TD]
[TD]11/1/16[/TD]
[TD]Q4 FY17[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]New Business[/TD]
[TD]12/1/16[/TD]
[TD]Q4 FY17[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]New Business[/TD]
[TD]1/1/16[/TD]
[TD]Q4 FY17[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cancellation[/TD]
[TD]5/1/16[/TD]
[TD]Q1 FY17[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Cancellation[/TD]
[TD]8/1/16[/TD]
[TD]Q1 FY17[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cancellation[/TD]
[TD]11/1/16[/TD]
[TD]Q1 FY17[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Cancellation[/TD]
[TD]12/1/16[/TD]
[TD]Q2 FY17[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Cancellation[/TD]
[TD]12/1/16[/TD]
[TD]Q4 FY17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Macropod]

Re: Combining Unique Values with multiple criteria array formula

Kindly don't start multiple threads on the same topic. Threads merged.