Count of NEW CUSTOMERS Q2 Q3 Q4

[makinmomb]

The formula on J2 is =IF(H2>100000,COUNT(J1:J$1)+1,"")

What are the formulas on D15, E15 16 E17 , F15 F16 F17 on the data below

acc	acc name	quart	JAN-MAR	APR-JUN	JUL-SEP	OCT-DEC	total
A198		Q1	109,800	x	x	x	109,800.00		1
A306		Q1	297,370	332,980	x	12,500	642,850.06		2
B089		Q1	80,564	x	x	x	80,564.20
A419		Q2 new	x	348,750	x	x	348,750.00		3
A424		Q2 new	x	1,172,055	335,600	58,430	1,566,085.00		4
D148		Q2 new	x	1,181,950	x	x	1,181,950.00		5
A429		Q3 new	x	x	174,870	x	174,870.00		6
A442		Q3 new	x	x	10,518,090	x	10,518,090.00		7
P329		Q3 new	x	x	1,522,500	6,880,250	8,402,750.00		8
B050		Q4 new	x	x	x	-32,100	- 32,100.00
			start Q1	new	repeat	total
		Q1		x	x	0
		Q2	x			0
		Q3	x			0
		Q4	x			0

<colgroup><col width="64" style="width: 48pt;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;" span="4"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="48" style="width: 36pt; mso-width-source: userset; mso-width-alt: 1755;"> <col width="21" style="width: 16pt; mso-width-source: userset; mso-width-alt: 768;"> <tbody> </tbody>

 

[makinmomb]

I am still half way sorted , the repeat clients formula, this one is partially working for Q2 and Q3
=COUNTIF($E$2:$E$11,">100000")-E15
( The result of Q4 should be 0 it counts as 1

	start Q1	new	repeat
Q1	2	x	x
Q2	x	3
Q3	x	3
Q4	x	0

<tbody>
</tbody>

I meant the result should be 3 since it did not count , 12500 and 58430 both the year end total of this clients are above 100,000

 

[alvin-chung]

Could you share the logic of "repeat" in more details, and which are the customers that should be in Q2 repeat and Q3 repeat?

 

[makinmomb]

Could you share the logic of "repeat" in more details, and which are the customers that should be in Q2 repeat and Q3 repeat?

q2 = 1 that is 332980 as the same client bought in Q1 297370
q3=1 that is 335,600
q4= 12500 ( total exceeding 100,000 ) 58430 ( total exceeding 100,000 )
and 6,880,250

 

[makinmomb]

Anybody to salvage my day ?

 

[alvin-chung]

Try this.

	A	B	C	D	E	F	G	H	I	J
1	acc	acc name	quart	JAN-MAR	APR-JUN	JUL-SEP	OCT-DEC	total
2	A198		Q1	109,800	x	x	x	109,800.00		1
3	A306		Q1	297,370	332,980	x	12,500	642,850.00		2
4	B089		Q1	80,564	x	x	x	80,564.00
5	A419		Q2 new	x	348,750	x	x	348,750.00		3
6	A424		Q2 new	x	1,172,055	335,600	58,430	1,566,085.00		4
7	D148		Q2 new	x	1,181,950	x	x	1,181,950.00		5
8	A429		Q3 new	x	x	174,870	x	174,870.00		6
9	A442		Q3 new	x	x	10,518,090	x	10,518,090.00		7
10	P329		Q3 new	x	x	1,522,500	6,880,250	8,402,750.00		8
11	B050		Q4 new	x	x	x	-32,100	-32,100.00
12
13				start Q1	new	repeat	total
14			Q1	2	0	0	2
15			Q2	0	3	1	4
16			Q3	0	3	1	4
17			Q4	0	0	3	3

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D14 =SUMPRODUCT((C$2:C$11=C14)*(H$2:H$11>100000)) E14 =SUMPRODUCT((C$2:C$11=C14&" new")*(H$2:H$11>100000)) F14 {=SUMPRODUCT(IF<font color="#008000">(<font color="#0000FF">(ISNUMBER(D$2:D$11)*<font color="#FF0000">(COLUMN(D$2:D$11)-3<value<font color="#804000">(RIGHT(C14,1)))</value+<font color="#0000FF">(ISNUMBER(E$2:E$11)*<font color="#FF0000">(COLUMN(E$2:E$11)-3<value(RIGHT(C14,1)))+(ISNUMBER(F$2:F$11)*(COLUMN(F$2:F$11)-3<value(RIGHT(C14,1)))>0,1,0),ISNUMBER(INDEX($D$2:$G$11,0,VALUE(RIGHT(C14,1))))*(H$2:H$11>100000))}</value</value <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER! ps: copy the formula down to row17

<tbody>
</tbody>

 

[makinmomb]

Try this.

	A	B	C	D	E	F	G	H	I	J
1	acc	acc name	quart	JAN-MAR	APR-JUN	JUL-SEP	OCT-DEC	total
2	A198		Q1	109,800	x	x	x	109,800.00		1
3	A306		Q1	297,370	332,980	x	12,500	642,850.00		2
4	B089		Q1	80,564	x	x	x	80,564.00
5	A419		Q2 new	x	348,750	x	x	348,750.00		3
6	A424		Q2 new	x	1,172,055	335,600	58,430	1,566,085.00		4
7	D148		Q2 new	x	1,181,950	x	x	1,181,950.00		5
8	A429		Q3 new	x	x	174,870	x	174,870.00		6
9	A442		Q3 new	x	x	10,518,090	x	10,518,090.00		7
10	P329		Q3 new	x	x	1,522,500	6,880,250	8,402,750.00		8
11	B050		Q4 new	x	x	x	-32,100	-32,100.00
12
13				start Q1	new	repeat	total
14			Q1	2	0	0	2
15			Q2	0	3	1	4
16			Q3	0	3	1	4
17			Q4	0	0	3	3

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D14 =SUMPRODUCT((C$2:C$11=C14)*(H$2:H$11>100000)) E14 =SUMPRODUCT((C$2:C$11=C14&" new")*(H$2:H$11>100000)) F14 {=SUMPRODUCT(IF<font color="#008000">(<font color="#0000ff">(ISNUMBER(D$2:D$11)*<font color="#ff0000">(COLUMN(D$2:D$11)-3<value<font color="#804000">(RIGHT(C14,1)))</value(ISNUMBER(E$2:E$11)*<font color="#ff0000">(COLUMN(E$2:E$11)-3<value(RIGHT(C14,1)))+(ISNUMBER(F$2:F$11)*(COLUMN(F$2:F$11)-3<value(RIGHT(C14,1)))>0,1,0),ISNUMBER(INDEX($D$2:$G$11,0,VALUE(RIGHT(C14,1))))*(H$2:H$11>100000))}</value</value <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER! ps: copy the formula down to row17

<tbody>
</tbody>

Super ALVIN I am sorted , but I most import formula isn't working , that on cell F14, advice if the SYNTAX is exact

 

[alvin-chung]

F14:F17 are formula arrays, did you press ctrl+shift+enter instead of just enter after copy/paste the formula from this post to your spreadsheet?

 

[makinmomb]

F14:F17 are formula arrays, did you press ctrl+shift+enter instead of just enter after copy/paste the formula from this post to your spreadsheet?

Yes I am sure to have pressed CTRL SHIFT ENTER knowing clearly it is an array formula

 

[makinmomb]

=SUMPRODUCT(IF((ISNUMBER(D$2:D$11)*(COLUMN(D$2:D$11)-3(RIGHT(C14,1)))(E$2:E$11)*(COLUMN(E$2:E$11)-3(RIGHT(C14,1)))+(ISNUMBER(F$2:F$11)*(COLUMN(F$2:F$11)-3(RIGHT(C14,1)))>0,1,0),ISNUMBER(INDEX($D$2:$G$11,0,VALUE(RIGHT(C14,1))))*(H$2:H$11>100000))

This is the formula and I am pressing CTRL SHIFT END exactly how it needs to be

 

[makinmomb]

Its also highlighting the words D$2:D$11 in yellow , not sure what that means

(D$2:D$11)*(COLUMN(D$2:D$11)-