Excel counting formula

[rameshppc]

STUDENT NAME	CLASS	ADMISSION NUMBER	FEES PAID
NAVEEN	LKG	4001	5000
KANNAN	UKG	4002	8000
NAVEEN	LKG	4001	2000
KARTHIK	1 ST	4003	12000
RAVI	UKG	4004	9000

<tbody>
</tbody>

I WANT TO GENERATE A REPORT LIKE BELOW,

CLASS	NO OF STUDENTS ADMITTED	TOTAL FEES PAID

<tbody>
</tbody>

KINDLY ADVISE FORMULA TO DO THE NEEDFUL.

 

[Aladin Akyurek]

The b10 cell out put not resulted as per the input data.

A1 = 5 and B1 = 7. These constitute the input. What is the expected output? The answer to this question is: 12.

What I'm asking is exactly this: What is your expected output, given the data you already posted.

 

[rameshppc]

STUDEN NAME CLASS ADMISSION DATE ADMISSION NUMBER FEES PAID REMARKS L. NAVEEN LKG 25/4/2018 7001 10,000 SENTHIL LKG 25/4/2018 7002 9,000 KUMAR 1 ST 26/4/2018 7003 11,000 DINESH LKG 26/4/2018 7004 10,000 KARAN UKG 26/4/2018 7005 7,000 MOORTHY 1 ST 26/4/2018 7006 15,000 VIGNESH LKG 26/4/2018 WAITING 1,000 RTE NAZER LKG 26/4/2018 7007 8,500 ASIF 1 ST 26/4/2018 7008 14,000 KARTHIK 5 TH 30/4/2018 7009 25,000 NAREN 3 RD 30/4/2018 7010 20,000 SUNDAR 1 ST 30/4/2018 7011 15,000 HASHINI UKG 30/4/2018 7012 11,500 LATHA UKG 30/4/2018 7013 12,000 NIRANJAN LKG 30/4/2018 WAITING 1,000 RTE KAMAL UKG 30/4/2018 7014 11,000 VINAY 1 ST 30/4/2018 7015 15,000 MEENA LKG 30/4/2018 7016 7,000 VENU LKG 30/4/2018 7017 10,000 CHANDRAN 1 ST 30/4/2018 7018 13,000 LAVANYA 5 TH 2/5/2018 7019 24,000 MOUSHMI 3 RD 2/5/2018 7020 20,000 <colgroup><col><col><col><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

Its my input data, now pls check the result of column B - "Admitted seats"

 

[Aladin Akyurek]

STUDEN NAME CLASS ADMISSION DATE ADMISSION NUMBER FEES PAID REMARKS L. NAVEEN LKG 25/4/2018 7001 10,000 SENTHIL LKG 25/4/2018 7002 9,000 KUMAR 1 ST 26/4/2018 7003 11,000 DINESH LKG 26/4/2018 7004 10,000 KARAN UKG 26/4/2018 7005 7,000 MOORTHY 1 ST 26/4/2018 7006 15,000 VIGNESH LKG 26/4/2018 WAITING 1,000 RTE NAZER LKG 26/4/2018 7007 8,500 ASIF 1 ST 26/4/2018 7008 14,000 KARTHIK 5 TH 30/4/2018 7009 25,000 NAREN 3 RD 30/4/2018 7010 20,000 SUNDAR 1 ST 30/4/2018 7011 15,000 HASHINI UKG 30/4/2018 7012 11,500 LATHA UKG 30/4/2018 7013 12,000 NIRANJAN LKG 30/4/2018 WAITING 1,000 RTE KAMAL UKG 30/4/2018 7014 11,000 VINAY 1 ST 30/4/2018 7015 15,000 MEENA LKG 30/4/2018 7016 7,000 VENU LKG 30/4/2018 7017 10,000 CHANDRAN 1 ST 30/4/2018 7018 13,000 LAVANYA 5 TH 2/5/2018 7019 24,000 MOUSHMI 3 RD 2/5/2018 7020 20,000 <colgroup><col><col><col><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

Its my input data, now pls check the result of column B - "Admitted seats"

New input. Great. What is the output which must obtain from this? What does "now pls check the result of column B - "Admitted seats"" mean?

 

[rameshppc]

Hi Aladin,

Pls note, my very first post contains only a sample of data, and now post# contains actual data.

I have said, "B" column resulted "1" as per my actual data only. its wrong and Pls check it now and advise.

 

[jorismoerings]

Hi,

We've tried to help but we can't move forward if you don't tell us what the correct answer to your question is.
So we can't help you based on a response like:

I have said, "B" column resulted "1" as per my actual data only. its wrong and Pls check it now and advise

.

Do the following:

1. take your sample data like you provided
2. create the setup of your report
3. create one line of the report with the correct answers.
4. post it.

This will help us in understanding what you're looking for instead of leaving us guessing what the correct answer should be.

Hope this helps.

 

[rameshppc]

Hi joris..

Thanks for your interference

I will post it..

 

[rameshppc]

STUDENT NAME	CLASS	ADMISSION DATE	ADMISSION NUMBER	FEES PAID	REMARKS
NAVEEN	LKG	25/04/2018	7001	10,000
SENTHIL	LKG	25/04/2018	7002	9,000
KUMAR	1 ST	26/04/2018	7003	11,000
DINESH	LKG	26/04/2018	7004	10,000
KARAN	UKG	26/04/2018	7005	7,000
KUMAR	1 ST	09/05/2018	7003	15,000
VIGNESH	LKG	09/05/2018	WAITING	1,000	RTE
SENTHIL	LKG	12/05/2018	7002	8,500
KUMAR	LKG	12/05/2018	7006	14,000

<tbody>
</tbody>

I AM GETTING BELOW RESULT.

S NO	CLASS	ALLOCATED SEATS	ADMITTED SEATS	BALANCE SEATS	FEES PAID
1	LKG	90	1	89	52,500
2	1 ST	90	1	89	26,000
3	UKG	90	1	89	7,000

<tbody>
</tbody>

NOW PLS ADVISE...

 

[Aladin Akyurek]

Are you getting that result or you want to get that result?

 

[rameshppc]

I am getting this result by using the given formula.

 

[jorismoerings]

Hi,

ok, this helps.

1. The formula given previously was based on the first data sample provided. The actual data (as you named it) provided later had an extra column within;
2. with one of your posts you've added extra criteria;

Both makes all formula's provided earlier, not useful any more.

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	STUDEN NAME	CLASS	ADMISSION DATE	ADMISSION NUMBER	FEES PAID	REMARKS		Report
2	L. NAVEEN	LKG	25-4-2018	7001	10000			CLASS	NO OF STUDENTS ADMITTED	TOTAL FEES PAID
3	SENTHIL	LKG	25-4-2018	7002	9000			LKG	6	54500
4	KUMAR	1 ST	26-4-2018	7003	11000			1 ST	6	83000
5	DINESH	LKG	26-4-2018	7004	10000			UKG	4	41500
6	KARAN	UKG	26-4-2018	7005	7000			5 TH	2	49000
7	MOORTHY	1 ST	26-4-2018	7006	15000			3 RD	2	40000
8	VIGNESH	LKG	26-4-2018	WAITING	1000	RTE
9	NAZER	LKG	26-4-2018	7007	8500
10	ASIF	1 ST	26-4-2018	7008	14000
11	KARTHIK	5 TH	30-4-2018	7009	25000
12	NAREN	3 RD	30-4-2018	7010	20000
13	SUNDAR	1 ST	30-4-2018	7011	15000
14	HASHINI	UKG	30-4-2018	7012	11500
15	LATHA	UKG	30-4-2018	7013	12000
16	NIRANJAN	LKG	30-4-2018	WAITING	1000	RTE
17	KAMAL	UKG	30-4-2018	7014	11000
18	VINAY	1 ST	30-4-2018	7015	15000
19	MEENA	LKG	30-4-2018	7016	7000
20	VENU	LKG	30-4-2018	7017	10000
21	CHANDRAN	1 ST	30-4-2018	7018	13000
22	LAVANYA	5 TH	2-5-2018	7019	24000
23	MOUSHMI	3 RD	2-5-2018	7020	20000
Sheet1

Cell Formulas
Range	Formula
I3	=SUMPRODUCT(($B$2:$B$23=$H3)*($A$2:$A$23=$A$2:$A$23)*($D$2:$D$23=$D$2:$D$23)*($D$2:$D$23<>"waiting"))
J3	=SUMPRODUCT(($B$2:$B$23=$H3)*($A$2:$A$23=$A$2:$A$23)*($D$2:$D$23=$D$2:$D$23)*($D$2:$D$23<>"waiting")*($E$2:$E$23))
H3	{=IFERROR(INDEX($B$2:$B$23,MATCH(0,COUNTIF($H$2:H2,$B$2:$B$23),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.