Sum of total proposed fees & pending fees month wise

M

mihir777

New member

Joined

May 28, 2016

Messages

24

Reaction score

0

Points

1

Excel Version(s)

2016

• Nov 3, 2020

• #1

Sir,

I require sum of Total Fees in fees summary sheet

SUM OF TOTAL PROPOSED FEES & PENDING FEES MONTH WISE

COURSE NAME		Total Fees Proposeed												Total Fees Paid												Total Fees Pending
Months	Centre	April	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	April	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	April	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
ECCE	Pune																									0	0	0	0	0	0	0	0	0	0	0	0
BASIC TAILORING	Pune																									0	0	0	0	0	0	0	0	0	0	0	0
ADV TAILORING	Pune																									0	0	0	0	0	0	0	0	0	0	0	0

___________________________________________________________________________________________________________________________________________________________________________________________

Sr. No

Roll No

Name

Course

phone no

Address

DOB

Age

Gender

Aadhar no.

Education Qualification

whether working

Caste

Married/ unmarried

Parent Name

DOJ

Total Fees

1st Ins Date

1st Installment

Mode of payment

2nd Ins Date

2st Installment

Mode of payment

Balance

Course Status

Remarks

1

100000

RAM KUMAR GAIKWAD

ADV TAILORING

xx

xx

DD-MM-YYYY

xx

Male

x

xx

yes

hindu

Un

xx

01-10-2020

10000.00

01-10-2020

5000.00

cash

01-11-2020

4000.00

cash

1000.00

Active

2

100001

RAHUL

BASIC BEAUTICIAN

xx

xx

DD-MM-YYYY

xx

Male

x

xx

yes

hindu

Un

xx

01-09-2020

10000.00

01-09-2020

1000.00

cash

01-09-2020

5000.00

cash

4000.00

Active

3

100002

DEEPAK

Adv BEAUTICIAN

xx

xx

DD-MM-YYYY

xx

Female

x

xx

yes

Muslim

Un

xx

01-06-2020

4000.00

01-06-2020

1000.00

cash

01-08-2020

2000.00

Paytm

1000.00

Active

4

100003

SHIVDARSHAN GUPTA

LEVEL-1 BATCH 1

xx

xx

DD-MM-YYYY

xx

Male

x

xx

yes

hindu

Un

xx

01-09-2020

2500.00

01-07-2020

1000.00

cash

01-09-2020

1000.00

cash

500.00

Active

5

100004

HIRA SINGH

LEVEL-1 BATCH 2

xx

xx

DD-MM-YYYY

xx

Male

x

xx

yes

hindu

Un

xx

01-06-2020

3000.00

01-06-2020

1000.00

cash

01-07-2020

2000.00

Paytm

0.00

Active

6

100005

MD. KHAN

LEVEL-1 BATCH 3(intern)

xx

xx

DD-MM-YYYY

xx

Male

x

xx

yes

hindu

Un

xx

01-08-2020

2500.00

01-08-2020

1000.00

cash

01-09-2020

500.00

Cheque

1000.00

Active

7

100006

SRINIVAS

LEVEL-2

xx

xx

DD-MM-YYYY

xx

Male

x

xx

yes

hindu

Un

xx

01-10-2020

2500.00

01-10-2020

1000.00

Paytm

01-11-2020

1500.00

Cheque

0.00

Active

8

100007

RAJU JADHAV

LEVEL-3 BATCH-1

xx

xx

DD-MM-YYYY

xx

Male

x

xx

yes

hindu

Un

xx

01-07-2020

5000.00

01-07-2020

1000.00

Paytm

01-08-2020

3500.00

Paytm

500.00

Active

9

100008

VISHAL GAIKWAD

MSCIT BATCH-1

xx

xx

DD-MM-YYYY

xx

Male

x

xx

yes

hindu

Un

xx

01-11-2020

4000.00

01-09-2020

1000.00

Paytm

01-10-2020

2500.00

Cheque

500.00

Active

10

100009

DINESH KUMAR

MSCIT BATCH-2

xx

xx

DD-MM-YYYY

xx

Male

x

xx

yes

hindu

Un

xx

01-06-2020

4000.00

01-06-2020

1000.00

Cheque

01-08-2020

3000.00

Paytm

0.00

Active

11

100010

SATISH GAIKWARD

IOT(Batch 1)

xx

xx

DD-MM-YYYY

xx

Male

x

xx

yes

hindu

Un

xx

01-06-2020

4000.00

01-06-2020

1000.00

Cheque

01-09-2020

1500.00

Paytm

1500.00

Active

Sheet attched for reference

 

Attachments

• Pune- Working sheet (With Formula) - Copy.xlsx

  112.4 KB · Views: 6

Bob Phillips

Super Moderator

Staff member

Joined

Mar 21, 2011

Messages

1,960

Reaction score

0

Points

36

Excel Version(s)

O365

• Nov 3, 2020

• #2

Try this

=SUMIFS('Student Data'!$Q$4:$Q$100,'Student Data'!$P$4:$P$100,">="&E$3,'Student Data'!$P$4:$P$100,"<="&E$4,'Student Data'!$D$4:$D$100,'Fees Summary'!$A5)

 

M

mihir777

New member

Joined

May 28, 2016

Messages

24

Reaction score

0

Points

1

Excel Version(s)

2016

• Nov 4, 2020

• #3

Bob Phillips said:

Try this

=SUMIFS('Student Data'!$Q$4:$Q$100,'Student Data'!$P$4:$P$100,">="&E$3,'Student Data'!$P$4:$P$100,"<="&E$4,'Student Data'!$D$4:$D$100,'Fees Summary'!$A5)

Click to expand...

Thanks

sir i also require total of fees paid course wise where total will be from Installment 1 and installment 2 .

 

Bob Phillips

Super Moderator

Staff member

Joined

Mar 21, 2011

Messages

1,960

Reaction score

0

Points

36

Excel Version(s)

O365

• Nov 5, 2020

• #4

Couldn't you just sium the balance in the same manner?

 

M

mihir777

New member

Joined

May 28, 2016

Messages

24

Reaction score

0

Points

1

Excel Version(s)

2016

• Nov 6, 2020

• #5

Bob Phillips said:

Couldn't you just sium the balance in the same manner?

Click to expand...

I tried doing with the same method but as i require total of Installment1 and Installment 2 course wise & Month Wise in Fees sheet (Total Fees Paid) so i am getting error.

Pls guide with the correct formula ..

 

Last edited: Nov 6, 2020

Bob Phillips

Super Moderator

Staff member

Joined

Mar 21, 2011

Messages

1,960

Reaction score

0

Points

36

Excel Version(s)

O365

• Nov 6, 2020

• #6

Then just sum those ranges. I fail to understand your difficulty, you have the approach provided already, just adapt it.