Excel counting formula

rameshppc

Board Regular
Joined
Jun 10, 2017
Messages
114
Office Version
  1. 2013
Platform
  1. Windows
[TABLE="width: 500"]
<tbody>[TR]
[TD]STUDENT NAME
[/TD]
[TD]CLASS
[/TD]
[TD]ADMISSION NUMBER
[/TD]
[TD]FEES PAID
[/TD]
[/TR]
[TR]
[TD]NAVEEN
[/TD]
[TD]LKG
[/TD]
[TD]4001
[/TD]
[TD]5000
[/TD]
[/TR]
[TR]
[TD]KANNAN
[/TD]
[TD]UKG
[/TD]
[TD]4002
[/TD]
[TD]8000
[/TD]
[/TR]
[TR]
[TD]NAVEEN
[/TD]
[TD]LKG
[/TD]
[TD]4001
[/TD]
[TD]2000
[/TD]
[/TR]
[TR]
[TD]KARTHIK
[/TD]
[TD]1 ST
[/TD]
[TD]4003
[/TD]
[TD]12000
[/TD]
[/TR]
[TR]
[TD]RAVI
[/TD]
[TD]UKG
[/TD]
[TD]4004
[/TD]
[TD]9000
[/TD]
[/TR]
</tbody>[/TABLE]

I WANT TO GENERATE A REPORT LIKE BELOW,
[TABLE="width: 500"]
<tbody>[TR]
[TD]CLASS
[/TD]
[TD]NO OF STUDENTS ADMITTED
[/TD]
[TD]TOTAL FEES PAID
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

KINDLY ADVISE FORMULA TO DO THE NEEDFUL.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hi,

try something like this:


Book1
ABCD
1STUDENT NAMECLASSADMISSION NUMBERFEES PAID
2NAVEENLKG40015000
3KANNANUKG40028000
4NAVEENLKG40012000
5KARTHIK1 ST400312000
6RAVIUKG40049000
7
8Report
9CLASSNO OF STUDENTS ADMITTEDTOTAL FEES PAID
10LKG17000
11UKG217000
121 ST112000
Sheet2
Cell Formulas
RangeFormula
C10=SUMIF($B$2:$B$6,A10,$D$2:$D$6)
A10{=IFERROR(INDEX($B$2:$B$6,MATCH(0,COUNTIF($A$9:A9,$B$2:$B$6),0)),"")}
B10{=SUM(IF((A10=$B$2:$B$6),1/COUNTIFS($B$2:$B$6,A10,$A$2:$A$6,$A$2:$A$6)))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD]STUDENT NAME[/TD]
[TD]CLASS[/TD]
[TD]ADMISSION NO[/TD]
[TD]FEES PAID[/TD]
[/TR]
[TR]
[TD]NAVEEN[/TD]
[TD]LKG[/TD]
[TD]4001[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]KANNAN[/TD]
[TD]UKG[/TD]
[TD]4002[/TD]
[TD]8000[/TD]
[/TR]
[TR]
[TD]NAVEEN[/TD]
[TD]1 ST[/TD]
[TD]4003[/TD]
[TD]12000[/TD]
[/TR]
[TR]
[TD]KARTHIK[/TD]
[TD]UKG[/TD]
[TD]4004[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]NAVEEN[/TD]
[TD]LKG[/TD]
[TD]4001[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]UMAR[/TD]
[TD]LKG[/TD]
[TD]BOOKING[/TD]
[TD]1000[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your immediate response..

A10 & C10 formulas are working nice..
B10 I getting the answer "1" for all rows (UKG, 1st, etc..)
Pls advise..

Also there is a chance of same student name in different class (A2 & A4) i shown in the above chart.
So advise formula based on this..

Also we need to omit in our report of the "Booking" mentioned in "Admission no" column.

Kindly advise to do the needful.
 
Upvote 0
Hi Aladin..

Cell B10 resulted "1" for all rows (LKG, UKG, !st etc..) instead of number of students admitted in the particular class.

Also consider to generate report for the same student name with different class asked in my previous post

Then, we need to omit the "Booking" mentioned in "Admission no" column.

kindly advise
 
Upvote 0

Forum statistics

Threads
1,224,585
Messages
6,179,703
Members
452,938
Latest member
babeneker

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