Excel counting formula

R

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.
 
Sort by date Sort by votes
rameshppc said:
The b10 cell out put not resulted as per the input data.
Click to expand...

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.
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 674"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]STUDEN NAME[/TD]
[TD]CLASS[/TD]
[TD]ADMISSION DATE[/TD]
[TD]ADMISSION NUMBER[/TD]
[TD]FEES PAID[/TD]
[TD]REMARKS[/TD]
[/TR]
[TR]
[TD]L. NAVEEN[/TD]
[TD]LKG[/TD]
[TD]25/4/2018[/TD]
[TD]7001[/TD]
[TD]10,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SENTHIL[/TD]
[TD]LKG[/TD]
[TD]25/4/2018[/TD]
[TD]7002[/TD]
[TD]9,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KUMAR[/TD]
[TD]1 ST[/TD]
[TD]26/4/2018[/TD]
[TD]7003[/TD]
[TD]11,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DINESH[/TD]
[TD]LKG[/TD]
[TD]26/4/2018[/TD]
[TD]7004[/TD]
[TD]10,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KARAN[/TD]
[TD]UKG[/TD]
[TD]26/4/2018[/TD]
[TD]7005[/TD]
[TD]7,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MOORTHY[/TD]
[TD]1 ST[/TD]
[TD]26/4/2018[/TD]
[TD]7006[/TD]
[TD]15,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VIGNESH[/TD]
[TD]LKG[/TD]
[TD]26/4/2018[/TD]
[TD]WAITING[/TD]
[TD]1,000[/TD]
[TD]RTE[/TD]
[/TR]
[TR]
[TD]NAZER[/TD]
[TD]LKG[/TD]
[TD]26/4/2018[/TD]
[TD]7007[/TD]
[TD]8,500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ASIF[/TD]
[TD]1 ST[/TD]
[TD]26/4/2018[/TD]
[TD]7008[/TD]
[TD]14,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KARTHIK[/TD]
[TD]5 TH[/TD]
[TD]30/4/2018[/TD]
[TD]7009[/TD]
[TD]25,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAREN[/TD]
[TD]3 RD[/TD]
[TD]30/4/2018[/TD]
[TD]7010[/TD]
[TD]20,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SUNDAR[/TD]
[TD]1 ST[/TD]
[TD]30/4/2018[/TD]
[TD]7011[/TD]
[TD]15,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HASHINI[/TD]
[TD]UKG[/TD]
[TD]30/4/2018[/TD]
[TD]7012[/TD]
[TD]11,500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LATHA[/TD]
[TD]UKG[/TD]
[TD]30/4/2018[/TD]
[TD]7013[/TD]
[TD]12,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NIRANJAN[/TD]
[TD]LKG[/TD]
[TD]30/4/2018[/TD]
[TD]WAITING[/TD]
[TD]1,000[/TD]
[TD]RTE[/TD]
[/TR]
[TR]
[TD]KAMAL[/TD]
[TD]UKG[/TD]
[TD]30/4/2018[/TD]
[TD]7014[/TD]
[TD]11,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VINAY[/TD]
[TD]1 ST[/TD]
[TD]30/4/2018[/TD]
[TD]7015[/TD]
[TD]15,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MEENA[/TD]
[TD]LKG[/TD]
[TD]30/4/2018[/TD]
[TD]7016[/TD]
[TD]7,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VENU[/TD]
[TD]LKG[/TD]
[TD]30/4/2018[/TD]
[TD]7017[/TD]
[TD]10,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CHANDRAN[/TD]
[TD]1 ST[/TD]
[TD]30/4/2018[/TD]
[TD]7018[/TD]
[TD]13,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LAVANYA[/TD]
[TD]5 TH[/TD]
[TD]2/5/2018[/TD]
[TD]7019[/TD]
[TD]24,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MOUSHMI[/TD]
[TD]3 RD[/TD]
[TD]2/5/2018[/TD]
[TD]7020[/TD]
[TD]20,000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Its my input data, now pls check the result of column B - "Admitted seats"
 
Upvote 0
rameshppc said:
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 674"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]STUDEN NAME[/TD]
[TD]CLASS[/TD]
[TD]ADMISSION DATE[/TD]
[TD]ADMISSION NUMBER[/TD]
[TD]FEES PAID[/TD]
[TD]REMARKS[/TD]
[/TR]
[TR]
[TD]L. NAVEEN[/TD]
[TD]LKG[/TD]
[TD]25/4/2018[/TD]
[TD]7001[/TD]
[TD]10,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SENTHIL[/TD]
[TD]LKG[/TD]
[TD]25/4/2018[/TD]
[TD]7002[/TD]
[TD]9,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KUMAR[/TD]
[TD]1 ST[/TD]
[TD]26/4/2018[/TD]
[TD]7003[/TD]
[TD]11,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DINESH[/TD]
[TD]LKG[/TD]
[TD]26/4/2018[/TD]
[TD]7004[/TD]
[TD]10,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KARAN[/TD]
[TD]UKG[/TD]
[TD]26/4/2018[/TD]
[TD]7005[/TD]
[TD]7,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MOORTHY[/TD]
[TD]1 ST[/TD]
[TD]26/4/2018[/TD]
[TD]7006[/TD]
[TD]15,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VIGNESH[/TD]
[TD]LKG[/TD]
[TD]26/4/2018[/TD]
[TD]WAITING[/TD]
[TD]1,000[/TD]
[TD]RTE[/TD]
[/TR]
[TR]
[TD]NAZER[/TD]
[TD]LKG[/TD]
[TD]26/4/2018[/TD]
[TD]7007[/TD]
[TD]8,500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ASIF[/TD]
[TD]1 ST[/TD]
[TD]26/4/2018[/TD]
[TD]7008[/TD]
[TD]14,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KARTHIK[/TD]
[TD]5 TH[/TD]
[TD]30/4/2018[/TD]
[TD]7009[/TD]
[TD]25,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAREN[/TD]
[TD]3 RD[/TD]
[TD]30/4/2018[/TD]
[TD]7010[/TD]
[TD]20,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SUNDAR[/TD]
[TD]1 ST[/TD]
[TD]30/4/2018[/TD]
[TD]7011[/TD]
[TD]15,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HASHINI[/TD]
[TD]UKG[/TD]
[TD]30/4/2018[/TD]
[TD]7012[/TD]
[TD]11,500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LATHA[/TD]
[TD]UKG[/TD]
[TD]30/4/2018[/TD]
[TD]7013[/TD]
[TD]12,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NIRANJAN[/TD]
[TD]LKG[/TD]
[TD]30/4/2018[/TD]
[TD]WAITING[/TD]
[TD]1,000[/TD]
[TD]RTE[/TD]
[/TR]
[TR]
[TD]KAMAL[/TD]
[TD]UKG[/TD]
[TD]30/4/2018[/TD]
[TD]7014[/TD]
[TD]11,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VINAY[/TD]
[TD]1 ST[/TD]
[TD]30/4/2018[/TD]
[TD]7015[/TD]
[TD]15,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MEENA[/TD]
[TD]LKG[/TD]
[TD]30/4/2018[/TD]
[TD]7016[/TD]
[TD]7,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VENU[/TD]
[TD]LKG[/TD]
[TD]30/4/2018[/TD]
[TD]7017[/TD]
[TD]10,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CHANDRAN[/TD]
[TD]1 ST[/TD]
[TD]30/4/2018[/TD]
[TD]7018[/TD]
[TD]13,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LAVANYA[/TD]
[TD]5 TH[/TD]
[TD]2/5/2018[/TD]
[TD]7019[/TD]
[TD]24,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MOUSHMI[/TD]
[TD]3 RD[/TD]
[TD]2/5/2018[/TD]
[TD]7020[/TD]
[TD]20,000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

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?
 
Upvote 0
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.
 
Upvote 0
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
Click to expand...
.

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.
 
Last edited:
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD]STUDENT NAME
[/TD]
[TD]CLASS
[/TD]
[TD]ADMISSION DATE
[/TD]
[TD]ADMISSION NUMBER
[/TD]
[TD]FEES PAID
[/TD]
[TD]REMARKS[/TD]
[/TR]
[TR]
[TD]NAVEEN
[/TD]
[TD]LKG
[/TD]
[TD]25/04/2018
[/TD]
[TD]7001
[/TD]
[TD]10,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SENTHIL
[/TD]
[TD]LKG
[/TD]
[TD]25/04/2018
[/TD]
[TD]7002
[/TD]
[TD]9,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KUMAR
[/TD]
[TD]1 ST
[/TD]
[TD]26/04/2018
[/TD]
[TD]7003
[/TD]
[TD]11,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DINESH
[/TD]
[TD]LKG
[/TD]
[TD]26/04/2018
[/TD]
[TD]7004
[/TD]
[TD]10,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KARAN
[/TD]
[TD]UKG
[/TD]
[TD]26/04/2018
[/TD]
[TD]7005
[/TD]
[TD]7,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KUMAR
[/TD]
[TD]1 ST
[/TD]
[TD]09/05/2018
[/TD]
[TD]7003
[/TD]
[TD]15,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VIGNESH
[/TD]
[TD]LKG
[/TD]
[TD]09/05/2018
[/TD]
[TD]WAITING
[/TD]
[TD]1,000
[/TD]
[TD]RTE
[/TD]
[/TR]
[TR]
[TD]SENTHIL
[/TD]
[TD]LKG
[/TD]
[TD]12/05/2018
[/TD]
[TD]7002
[/TD]
[TD]8,500
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KUMAR
[/TD]
[TD]LKG
[/TD]
[TD]12/05/2018
[/TD]
[TD]7006
[/TD]
[TD]14,000
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I AM GETTING BELOW RESULT.

[TABLE="width: 500"]
<tbody>[TR]
[TD]S NO
[/TD]
[TD]CLASS
[/TD]
[TD]ALLOCATED SEATS
[/TD]
[TD]ADMITTED SEATS
[/TD]
[TD]BALANCE SEATS
[/TD]
[TD]FEES PAID
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]LKG
[/TD]
[TD]90
[/TD]
[TD]1
[/TD]
[TD]89
[/TD]
[TD]52,500
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1 ST
[/TD]
[TD]90
[/TD]
[TD]1
[/TD]
[TD]89
[/TD]
[TD]26,000
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]UKG
[/TD]
[TD]90
[/TD]
[TD]1
[/TD]
[TD]89
[/TD]
[TD]7,000
[/TD]
[/TR]
</tbody>[/TABLE]

NOW PLS ADVISE...
 
Upvote 0
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
ABCDEFGHIJ
1STUDEN NAMECLASSADMISSION DATEADMISSION NUMBERFEES PAIDREMARKSReport
2L. NAVEENLKG25-4-2018700110000CLASSNO OF STUDENTS ADMITTEDTOTAL FEES PAID
3SENTHILLKG25-4-201870029000LKG654500
4KUMAR1 ST26-4-20187003110001 ST683000
5DINESHLKG26-4-2018700410000UKG441500
6KARANUKG26-4-2018700570005 TH249000
7MOORTHY1 ST26-4-20187006150003 RD240000
8VIGNESHLKG26-4-2018WAITING1000RTE
9NAZERLKG26-4-201870078500
10ASIF1 ST26-4-2018700814000
11KARTHIK5 TH30-4-2018700925000
12NAREN3 RD30-4-2018701020000
13SUNDAR1 ST30-4-2018701115000
14HASHINIUKG30-4-2018701211500
15LATHAUKG30-4-2018701312000
16NIRANJANLKG30-4-2018WAITING1000RTE
17KAMALUKG30-4-2018701411000
18VINAY1 ST30-4-2018701515000
19MEENALKG30-4-201870167000
20VENULKG30-4-2018701710000
21CHANDRAN1 ST30-4-2018701813000
22LAVANYA5 TH2-5-2018701924000
23MOUSHMI3 RD2-5-2018702020000
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Need to Randomly fill columns from other columns till certain criteria is met (stock finish)
Replies
0
Views
642
is2_egypt
I

Forum statistics

Threads
1,223,714
Messages
6,174,052
Members
452,542
Latest member
Bricklin

Share this page

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