Help me to get the count of Paid Amt against Groups

[Sam_D_Ben]

Hi team,

I am looking for an option than Pivot to get the Count of Paid Amounts against each Groups using a formula since its a huge data; tried my best. Thanks in Advance.

Excel 2007
	A	B	C	D	E	F	G	H
1	CUSTOMER_CODE	MSISDN	Allocation	Agency	Paid_Dec 05		Agency	Paid Count
2	1.109947	9846042418	90+	Group C	500		Group A	4
3	1.179018	9846102010	90+	Group C	250		Group B	3
4	1.187782	9846019260	60-90	Group B	200		Group C	7
5	1.191232	9846064178	90+	Group C
6	1.227169	9846252525	60-90	Group B
7	1.243288	9846013130	60-90	Group B	250
8	1.268491	9846280015	90+	Group C	500
9	1.282494	9846022782	90+	Group A	200
10	1.312255	9846233924	90+	Group C
11	1.426334	9846300838	90+	Group A
12	1.465313	9846333092	60-90	Group A	150
13	1.494015	9846022918	90+	Group C	875
14	1.521129	9846106889	90+	Group C
15	1.521991	9946037200	60-90	Group B
16	1.522377	9946553252	90+	Group C	600
17	1.526947	9846028698	60-90	Group B	350
18	1.537406	9846755444	90+	Group C
19	1.549041	9846703224	90+	Group C	750
20	1.1752983	9946444129	90+	Group A
21	1.1767847	9946445484	90+	Group C
22	1.1896776	9946448095	90+	Group A	200
23	1.2235584	9946107984	90+	Group A
24	1.224278	9946488901	90+	Group C	600
25	1.2833481	9846177888	60-90	Group A	500
Sheet1

In the above data, 4,3,7 is the count of Paid amount against each group.

Thank You,
Muz

 

[Robert Mika]

Hi team,

I am looking for an option than Pivot to get the Count of Paid Amounts against each Groups using a formula since its a huge data; tried my best. Thanks in Advance.

Excel 2007

	A	B	C	D	E	F	G	H
1	CUSTOMER_CODE	MSISDN	Allocation	Agency	Paid_Dec 05		Agency	Paid Count
2	1.109947	9846042418	90+	Group C	500		Group A	4
3	1.179018	9846102010	90+	Group C	250		Group B	3
4	1.187782	9846019260	60-90	Group B	200		Group C	7
5	1.191232	9846064178	90+	Group C
6	1.227169	9846252525	60-90	Group B
7	1.243288	9846013130	60-90	Group B	250
8	1.268491	9846280015	90+	Group C	500
9	1.282494	9846022782	90+	Group A	200
10	1.312255	9846233924	90+	Group C
11	1.426334	9846300838	90+	Group A
12	1.465313	9846333092	60-90	Group A	150
13	1.494015	9846022918	90+	Group C	875
14	1.521129	9846106889	90+	Group C
15	1.521991	9946037200	60-90	Group B
16	1.522377	9946553252	90+	Group C	600
17	1.526947	9846028698	60-90	Group B	350
18	1.537406	9846755444	90+	Group C
19	1.549041	9846703224	90+	Group C	750
20	1.1752983	9946444129	90+	Group A
21	1.1767847	9946445484	90+	Group C
22	1.1896776	9946448095	90+	Group A	200
23	1.2235584	9946107984	90+	Group A
24	1.224278	9946488901	90+	Group C	600
25	1.2833481	9846177888	60-90	Group A	500

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

In the above data, 4,3,7 is the count of Paid amount against each group.

Thank You,
Muz

Not ste if than Pivot means Pivot or not.
Here is a formula solution

Excel 2010
	A	B	C	D	E	F	G	H	I
1	CUSTOMER_CODE	MSISDN	Allocation	Agency	Paid_Dec 05		Agency	Excel 07/10	Excel 03
2	1.109947	9.85E+09	90+	Group C	500		Group A	4	4
3	1.179018	9.85E+09	90+	Group C	250		Group B	3	3
4	1.187782	9.85E+09	60-90	Group B	200		Group C	7	7
5	1.191232	9.85E+09	90+	Group C
6	1.227169	9.85E+09	60-90	Group B
7	1.243288	9.85E+09	60-90	Group B	250
8	1.268491	9.85E+09	90+	Group C	500
9	1.282494	9.85E+09	90+	Group A	200
10	1.312255	9.85E+09	90+	Group C
11	1.426334	9.85E+09	90+	Group A
12	1.465313	9.85E+09	60-90	Group A	150
13	1.494015	9.85E+09	90+	Group C	875
14	1.521129	9.85E+09	90+	Group C
15	1.521991	9.95E+09	60-90	Group B
16	1.522377	9.95E+09	90+	Group C	600
17	1.526947	9.85E+09	60-90	Group B	350
18	1.537406	9.85E+09	90+	Group C
Sheet1

Cell Formulas
Range	Formula
H2	=COUNTIFS($D$2:$D$25,G2,$E$2:$E$25,"<>"&"")
I2	=SUMPRODUCT(--($D$2:$D$25=G2),--($E$2:$E$25<>""))

 

[Sam_D_Ben]

Its Awesome Rebert,

I feel pity with myself, At times my stupid brain won't work. I was looking for this part{ "<>"&"" }, This part was missing in mine..he he he

So here we go - =COUNTIFS(Base!BB:BB,'05Dec12 Per'!B4,Base!C:C,'05Dec12 Per'!C4,Base!BO:BO,"<>"&"")

Thanks a lot,
Muz