Counting between status based on unique ID

[creative999]

I want to know how many opportunities were:

Created, ‘OPP CREATED’ through to a quote being sent, ‘QUOTE SENT’ (Col B)

And separately:

Created, ‘OPP CREATED’ through to being won, ‘OPP WON’ (Col B)
But the count should exclude any opportunities which have a overall stage of ‘OPP CANCELLED’ (Col P).

The opportunities have a unique Sale ID (Col Q) but these may not be in order in the actual worksheet.
Any suggestions?

Book9.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	DATE	STAGE	INITIAL QUOTE	SALE FIGURE	X	X	X	X	X	X	X	X	X	X	X	OPP OVERALL STAGE	SALE ID
2	3/15/20	OPP CREATED	100		X	X	X	X	X	X	X	X	X	X	X	QUOTE SENT	290
3	3/15/20	CUSTOMER INTERESTED	100		X	X	X	X	X	X	X	X	X	X	X	QUOTE SENT	290
4	3/15/20	QUOTE SENT	100		X	X	X	X	X	X	X	X	X	X	X	QUOTE SENT	290
5	6/28/20	OPP CREATED	85		X	X	X	X	X	X	X	X	X	X	X	OPP CANCELLED	530
6	6/28/20	OPP CANCELLED	85		X	X	X	X	X	X	X	X	X	X	X	OPP CANCELLED	530
7	6/2/20	OPP CREATED	120		X	X	X	X	X	X	X	X	X	X	X	CUSTOMER INTERESTED	124
8	6/2/20	CUSTOMER INTERESTED	120		X	X	X	X	X	X	X	X	X	X	X	CUSTOMER INTERESTED	124
9	6/2/20	CUSTOMER INTERESTED	120		X	X	X	X	X	X	X	X	X	X	X	CUSTOMER INTERESTED	124
10	6/2/20	CUSTOMER INTERESTED	120		X	X	X	X	X	X	X	X	X	X	X	CUSTOMER INTERESTED	124
11	6/2/20	CUSTOMER INTERESTED	120		X	X	X	X	X	X	X	X	X	X	X	CUSTOMER INTERESTED	124
12	6/2/20	CUSTOMER INTERESTED	120		X	X	X	X	X	X	X	X	X	X	X	CUSTOMER INTERESTED	124
13	6/2/20	CUSTOMER INTERESTED	120		X	X	X	X	X	X	X	X	X	X	X	CUSTOMER INTERESTED	124
14	3/16/20	OPP CREATED	45		X	X	X	X	X	X	X	X	X	X	X	OPP CREATED	133
15	7/21/20	OPP CREATED	125		X	X	X	X	X	X	X	X	X	X	X	OPP CANCELLED	546
16	7/21/20	CUSTOMER INTERESTED	125		X	X	X	X	X	X	X	X	X	X	X	OPP CANCELLED	546
17	7/21/20	CUSTOMER INTERESTED	125		X	X	X	X	X	X	X	X	X	X	X	OPP CANCELLED	546
18	7/21/20	QUOTE SENT	125		X	X	X	X	X	X	X	X	X	X	X	OPP CANCELLED	546
19	7/21/20	QUOTE SENT	125		X	X	X	X	X	X	X	X	X	X	X	OPP CANCELLED	546
20	7/21/20	QUOTE SENT	125		X	X	X	X	X	X	X	X	X	X	X	OPP CANCELLED	546
21	7/21/20	QUOTE SENT	125		X	X	X	X	X	X	X	X	X	X	X	OPP CANCELLED	546
22	7/21/20	OPP CANCELLED	125		X	X	X	X	X	X	X	X	X	X	X	OPP CANCELLED	546
23	1/27/21	OPP CREATED	190		X	X	X	X	X	X	X	X	X	X	X	QUOTE SENT	873
24	1/27/21	QUOTE SENT	190		X	X	X	X	X	X	X	X	X	X	X	QUOTE SENT	873
25	1/27/21	QUOTE SENT	190		X	X	X	X	X	X	X	X	X	X	X	QUOTE SENT	873
26	1/27/21	QUOTE SENT	190		X	X	X	X	X	X	X	X	X	X	X	QUOTE SENT	873
27	6/23/20	OPP CREATED	130	140.00	X	X	X	X	X	X	X	X	X	X	X	OPP WON	944
28	6/23/20	CUSTOMER INTERESTED	130	140.00	X	X	X	X	X	X	X	X	X	X	X	OPP WON	944
29	6/23/20	CUSTOMER INTERESTED	130	140.00	X	X	X	X	X	X	X	X	X	X	X	OPP WON	944
30	6/23/20	QUOTE SENT	130	140.00	X	X	X	X	X	X	X	X	X	X	X	OPP WON	944
31	6/23/20	QUOTE SENT	130	140.00	X	X	X	X	X	X	X	X	X	X	X	OPP WON	944
32	6/23/20	QUOTE SENT	130	140.00	X	X	X	X	X	X	X	X	X	X	X	OPP WON	944
33	6/23/20	CUSTOMER INTERESTED	130	140.00	X	X	X	X	X	X	X	X	X	X	X	OPP WON	944
34	6/23/20	CUSTOMER INTERESTED	130	140.00	X	X	X	X	X	X	X	X	X	X	X	OPP WON	944
35	6/23/20	QUOTE SENT	130	140.00	X	X	X	X	X	X	X	X	X	X	X	OPP WON	944
36	6/23/20	QUOTE SENT	130	140.00	X	X	X	X	X	X	X	X	X	X	X	OPP WON	944
37	6/23/20	OPP WON	130	140.00	X	X	X	X	X	X	X	X	X	X	X	OPP WON	944
Sheet1

 

[jasonb75]

Want to be sure I'm reading this correctly

Created, ‘OPP CREATED’ through to a quote being sent, ‘QUOTE SENT’ (Col B)

Are you saying count those where both appear in column B for a sale ID (290,546,873 and 944 in the mini sheet)?

Does the exclusion apply to this part of the question, or only the second part?

 

[creative999]

It’s the last Quote Sent for each unique sale id.
And separate formulae for the last Opp Won for each unique sale id.
Both should exclude if there was a status of cancelled for the unique sale id.

Hope that makes sense.

 

[jasonb75]

Hope that makes sense.

Less than it did before unfortunately,

Created, ‘OPP CREATED’ through to a quote being sent, ‘QUOTE SENT’ (Col B)

Are we looking at both in column B, or one in B and the other in P?

It would help if you gave us the expected results for the example instead of leaving us to guess. Highlighting the rows that the results are taken from would also be useful.

Given that columns C:O are not relevant to the question, you could also hide them in excel before creating your mini sheet in order to reduce the clutter.

 

[creative999]

Hope this helps:

Book9.xlsx
	A	B														P	Q	R	S	T
1	DATE	STAGE														OPP OVERALL STAGE	SALE ID
2	3/15/20	OPP CREATED														QUOTE SENT	290		QUOTE SENT	3
3	3/15/20	CUSTOMER INTERESTED														QUOTE SENT	290		OPP WON	2
4	3/15/20	QUOTE SENT														QUOTE SENT	290
5	6/28/20	OPP CREATED														OPP CANCELLED	530
6	6/28/20	OPP CANCELLED														OPP CANCELLED	530
7	6/2/20	OPP CREATED														OPP WON	124
8	6/2/20	CUSTOMER INTERESTED														OPP WON	124
9	6/2/20	CUSTOMER INTERESTED														OPP WON	124
10	6/2/20	CUSTOMER INTERESTED														OPP WON	124
11	6/2/20	OPP WON														OPP WON	124
12	6/2/20	OPP WON														OPP WON	124
13	6/2/20	OPP WON														OPP WON	124
14	3/16/20	OPP CREATED														OPP CREATED	133
15	7/21/20	OPP CREATED														OPP CANCELLED	546
16	7/21/20	CUSTOMER INTERESTED														OPP CANCELLED	546
17	7/21/20	CUSTOMER INTERESTED														OPP CANCELLED	546
18	7/21/20	QUOTE SENT														OPP CANCELLED	546
19	7/21/20	QUOTE SENT														OPP CANCELLED	546
20	7/21/20	QUOTE SENT														OPP CANCELLED	546
21	7/21/20	QUOTE SENT														OPP CANCELLED	546
22	7/21/20	OPP CANCELLED														OPP CANCELLED	546
23	1/27/21	OPP CREATED														QUOTE SENT	873
24	1/27/21	QUOTE SENT														QUOTE SENT	873
25	1/27/21	QUOTE SENT														QUOTE SENT	873
26	1/27/21	QUOTE SENT														QUOTE SENT	873
27	6/23/20	OPP CREATED														OPP WON	944
28	6/23/20	CUSTOMER INTERESTED														OPP WON	944
29	6/23/20	CUSTOMER INTERESTED														OPP WON	944
30	6/23/20	QUOTE SENT														OPP WON	944
31	6/23/20	QUOTE SENT														OPP WON	944
32	6/23/20	QUOTE SENT														OPP WON	944
33	6/23/20	CUSTOMER INTERESTED														OPP WON	944
34	6/23/20	CUSTOMER INTERESTED														OPP WON	944
35	6/23/20	QUOTE SENT														OPP WON	944
36	6/23/20	QUOTE SENT														OPP WON	944
37	6/23/20	OPP WON														OPP WON	944
Sheet1

 

[jasonb75]

Yep, that is close to what I was thinking. Now I just have to find a way to make that into a formula ?

 

[jasonb75]

This works with office 365.

=IFERROR(ROWS(UNIQUE(FILTER(Q2:Q37,(B2:B37="QUOTE SENT")*(P2:P37<>"OPP CANCELLED")))),0)

=IFERROR(ROWS(UNIQUE(FILTER(Q2:Q37,(B2:B37="OPP WON")*(P2:P37<>"OPP CANCELLED")))),0)