Excel counting formula

rameshppc

Board Regular
Joined
Jun 10, 2017
Messages
114
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
STUDENT NAMECLASSADMISSION NOFEES PAID
NAVEENLKG40015000
KANNANUKG40028000
NAVEEN1 ST400312000
KARTHIKUKG40045000
NAVEENLKG40012000
UMARLKGBOOKING1000

<tbody>
</tbody>

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,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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