if condition

[uswyne]

Fee Structure
Admission	Security	Tution Fee	C.Fee
25000	25000	10000	1000

<tbody>
</tbody>

A1	B1	C1	D1	E1	F1	G1
Admission	Security	Month 1	Month 2	Tution Fee	C.Fee	Total
25000	25000	Aug-17	Sep-17	20000	2000	72000
25000	25000	Aug-17		10000	1000	61000
manually enter	manually enter	manually enter	manually enter	Formula	Formula	formula

<tbody>
</tbody>

I want if C1 and D1 are filled with month name than E1 and F1 charge for fee for 2 months (multiply by 2) and if one is filled C1 or D1 than 1 month fee charge.

 

[Legacy 143009]

Let say your C.free value is placed in K1 then:

F3=IF(AND(ISNUMBER(DATEVALUE(C3)),ISNUMBER(DATEVALUE(D3))),$K$1*2,IF(OR(ISNUMBER(DATEVALUE(C3)),ISNUMBER(DATEVALUE(D3))),$K$1,"0"))
Tihs formula will return "0" if there are no dates in C and D. If date data will exist in anyways then, you may delete ,"0" expression (with comma).
You can use the same formula in E3 for the Tution Fee.

 

[Legacy 143009]

If 2 is not a fixed multiplier and changes according to number of months then;


F3=IF(AND(ISNUMBER(DATEVALUE(C3)),ISNUMBER(DATEVALUE(D3))),$K$1*(MONTH(D3)-MONTH(C3))+1,IF(OR(ISNUMBER(DATEVALUE(C3)),ISNUMBER(DATEVALUE(D3))),$K$1,"0"))

 

[uswyne]

thanks for your response.

the formula I have entered but too many arguments error occur.

Let say your C.free value is placed in K1 then:

F3=IF(AND(ISNUMBER(DATEVALUE(C3)),ISNUMBER(DATEVALUE(D3))),$K$1*2,IF(OR(ISNUMBER(DATEVALUE(C3)),ISNUMBER(DATEVALUE(D3))),$K$1,"0"))
Tihs formula will return "0" if there are no dates in C and D. If date data will exist in anyways then, you may delete ,"0" expression (with comma).
You can use the same formula in E3 for the Tution Fee.

 

[Legacy 143009]

thanks for your response.

the formula I have entered but too many arguments error occur.

Ok tnen, try this one:

=IF(AND(ISNUMBER(C3),ISNUMBER(D3)),$K$1*2,IF(OR(ISNUMBER(C3),ISNUMBER(D3)),$K$1,"0"))

This works fine for me.

 

[jtakw]

Hi,

How about this, change cell references for Fee Structure Table as required:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Admission	Security	Month 1	Month 2	Tution Fee	C.Fee	Total		Fee Structure
2	25000	25000	17-Aug	17-Sep	20000	2000	72000		Admission	Security	Tution Fee	C.Fee
3	25000	25000	17-Aug		10000	1000	61000		25000	25000	10000	1000
4	25000	25000			0	0	50000
5	25000	25000	17-Aug	17-Sep	20000	2000	72000
Sheet9

Cell Formulas
Range	Formula
E2	=--COUNT(C2:D2)*K$3
F2	=--COUNT(C2:D2)*L$3
G2	=SUM(A2,B2,E2,F2)

E2, F2, G2 formulae copied down.

 

[uswyne]

this formula shows 0

If 2 is not a fixed multiplier and changes according to number of months then;


F3=IF(AND(ISNUMBER(DATEVALUE(C3)),ISNUMBER(DATEVALUE(D3))),$K$1*(MONTH(D3)-MONTH(C3))+1,IF(OR(ISNUMBER(DATEVALUE(C3)),ISNUMBER(DATEVALUE(D3))),$K$1,"0"))

 

[jtakw]

Hi,

Additional formulae for additional months, if duration might be more than 2 months, then use formulae in Row 6:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Admission	Security	Month 1	Month 2	Tution Fee	C.Fee	Total		Fee Structure
2	25000	25000	17-Aug	17-Sep	20000	2000	72000		Admission	Security	Tution Fee	C.Fee
3	25000	25000	17-Aug		10000	1000	61000		25000	25000	10000	1000
4	25000	25000			0	0	50000
5	25000	25000	17-Aug	17-Sep	20000	2000	72000
6	25000	25000	17-Aug	17-Dec	40000	4000	94000
7	25000	25000	17-Aug		10000	1000	61000
8	25000	25000			0	0	50000
Sheet9

Cell Formulas
Range	Formula
E2	=--COUNT(C2:D2)*K$3
E6	=IF(C6="",0,IF(COUNT(C6:D6)=2,(MONTH(D6)-MONTH(C6))*K$3,K$3))
F2	=--COUNT(C2:D2)*L$3
F6	=IF(C6="",0,IF(COUNT(C6:D6)=2,(MONTH(D6)-MONTH(C6))*L$3,L$3))
G2	=SUM(A2,B2,E2,F2)
G6	=SUM(A6,B6,E6,F6)

 

[uswyne]

Sorry for late reply.

this works me too.

thanks

Ok tnen, try this one:

=IF(AND(ISNUMBER(C3),ISNUMBER(D3)),$K$1*2,IF(OR(ISNUMBER(C3),ISNUMBER(D3)),$K$1,"0"))

This works fine for me.