Count Single and Multiple Number

[ibmy]

Hi,

4.5.xlsb
	B	C	D	E	F	G
1	Data				Single, =1	Multiple, >1
2	1.18981			1.18981	5	2
3	1.18975			1.18975	2	0
4	1.18981			1.18955	2	1
5	1.18955			1.11051	1	0
6	1.18981
7	1.18981
8	1.18981
9	1.18981
10	1.18981
11	1.11051
12	1.18981
13	1.18955
14	1.18981
15	1.18975
16	1.18981
17	1.18981
18	1.18955
19	1.18955
20	1.18981
templete 1

Example :
E2 is 1.18981
then, Count Single in Column B which is appear in B2, B4, B12, B14, B20....result in F2
then, Count Multiple in Column B which is appear in B6-B10, B16-B17.....result in G2

p/s: Real data in Column B ranging from 50k-70k rows, and Column E (distinct value from Column B) ranging from 200-400 rows.

 

[bebo021999]

Try with formula first for your sample size (with larger data, if excel hang out, if you refer to a VBA solution, I will come back)

Book1
	B	C	D	E	F	G
1	Data				Single, =1	Multiple, >1
2	1.18981			1.18981	5	2
3	1.18975			1.18975	2	0
4	1.18981			1.18955	2	1
5	1.18955			1.11051	1	0
6	1.18981
7	1.18981
8	1.18981
9	1.18981
10	1.18981
11	1.11051
12	1.18981
13	1.18955
14	1.18981
15	1.18975
16	1.18981
17	1.18981
18	1.18955
19	1.18955
20	1.18981
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=SUMPRODUCT(--($B$2:$B$20=$E2), --($B$1:$B$19<>$E2), --($B$3:$B$21<>$E2))
G2:G5	G2	=SUMPRODUCT(--($B$2:$B$20=$E2), --($B$1:$B$19<>$E2), --($B$3:$B$21=$E2))

 

[ibmy]

@bebo021999 thanks for solution.

The formula work acceptable fast for small data.
I think I need VBA solution for larger data.