Count of NEW CUSTOMERS Q2 Q3 Q4

M

makinmomb

Active Member
Joined
Dec 23, 2013
Messages
401
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-JUNJUL-SEPOCT-DECtotal
A198 Q1 109,800 xxx 109,800.00 1
A306 Q1 297,370 332,980 x12,500 642,850.06 2
B089 Q1 80,564 xxx 80,564.20
A419 Q2 new x348,750 xx 348,750.00 3
A424 Q2 new x1,172,055 335,600 58,430 1,566,085.00 4
D148 Q2 new x1,181,950 xx 1,181,950.00 5
A429 Q3 new xx174,870 x 174,870.00 6
A442 Q3 new xx10,518,090 x 10,518,090.00 7
P329 Q3 new xx1,522,500 6,880,250 8,402,750.00 8
B050 Q4 new xxx-32,100 - 32,100.00
start Q1newrepeattotal
Q1 xx0
Q2x 0
Q3x 0
Q4x 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>
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
d14 that is first quarter number customers
e15 that is the number of new customers q2
e16 that is the number of new customers q3
e17 that is the number of new customers q4
f15 that is the number of repeat clients q2
f16 that is the number of repeat clients q3
f17 that is the number of repeat clients q4
A customer is only counted if the total is above 100,000 formula plugged into J2
=IF(H2>100000,COUNT(J1:J$1)+1,"")
 
Upvote 0
Closer attention to get the scenario

The result of Q2 new is = 3
While the resul o Q2 repeat = 1 , A306 being a repeat client who bought with us on Q1 and Q2 again
 
Upvote 0
makinmomb said:
Closer attention to get the scenario

The result of Q2 new is = 3
While the resul o Q2 repeat = 1 , A306 being a repeat client who bought with us on Q1 and Q2 again
Click to expand...

I am self sorted except the q4 repeat which reads 4 instead of 0, can someone assist me whether I am correct before I apply it on huge data

start Q1newrepeattotal
Q12xx
Q2x31
Q3x31
Q4x04
d4 =..=COUNTIFS(D2:D11,">100000",$C$2:$C$11,"=Q1")
e15 =..=COUNTIFS(E2:E11,">100000",$C$2:$C$11,"=Q2 new")
e16 =..=COUNTIFS(F2:F11,">100000",$C$2:$C$11,"=Q3 new")
e17 =..=COUNTIFS(G2:G11,">100000",$C$2:$C$11,"=Q4 new")
f15 =..=COUNT(E2:E11)-E15
f16 =..=COUNT(F2:F11)-E16
f17 =..=COUNT(G2:G11)-E17
<colgroup><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"> <tbody> </tbody>
 
Upvote 0
I think the formulas would work on this small table but could be wrong

Let me re put the argument again, the formula =COUNTIFS(D2:D11,">100000",$C$2:$C$11,"=Q1")
has correctly not counted the 80,564 , what is the total was above 10,000 assuming 20,000 worth
of goods were sold to the client Q3
 
Upvote 0
In short I do not know to add the third argument in the formula t go check the total which is on column H
..=COUNTIFS(E2:E11,">100000",$C$2:$C$11,"=Q2 new")

 
Upvote 0
I'm getting confused...
It seems to me you've found a solution, but would like to enhance the formula to include checking of total>100,000?

=COUNTIFS(E2:E11,">100000",$C$2:$C$11,"=Q2 new",h2:h11,">100000") is what you're looking for?
 
Upvote 0
alvin-chung said:
I'm getting confused...
It seems to me you've found a solution, but would like to enhance the formula to include checking of total>100,000?

=COUNTIFS(E2:E11,">100000",$C$2:$C$11,"=Q2 new",h2:h11,">100000") is what you're looking for?
Click to expand...

Yes, thanks
 
Upvote 0
makinmomb said:
Yes, thanks
Click to expand...

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 Q1newrepeat
Q12xx
Q2x3
Q3x3
Q4x0
<colgroup><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="3"> <tbody> </tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
920
Godders199
G
D
  • Question
Complex Bullet POint Addition
Replies
1
Views
495
DRSteele
DRSteele
T
  • Question
Macro needed to add items to worksheet
Replies
3
Views
966
onlyadrafter
O
B
  • Question
Need Combination of dollar amounts that match entered dollar amount
Replies
6
Views
1K
lrobbo314
lrobbo314
C
  • Question
LARGE formula that finds consecutive numbers
2
Replies
12
Views
1K
DanteAmor
DanteAmor

Forum statistics

Threads
1,214,911
Messages
6,122,199
Members
449,072
Latest member
DW Draft

Share this page

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