Count number of entries and sum

[Muthukrishnan V]

Number of entries.xlsx
	A	B	C	D	E	F	G
1
2		Excel 365
3		Columns B to G Numericals
4		Amount		From	To	No.of entries	Total Amount
5		401		0	500	4	971
6		718		501	1000	1	718
7		20741		1001	2000	2	3138
8		8700		2001	5000	0	0
9		13		5001	10000	3	26420
10		1459		10001	15000	0	0
11		478		15001	20000	1	15756
12		15756		20001	25000	2	41541
13		20800		25001	50000	1	34007
14		8819		50001		0	0
15		1679
16		8901
17		79
18		34007
19		122551				14	122551
20
21		From Column B, I want to accomplish Columns F and G with formula.
22		Kindly help me with Formula for Columns F and G. Thanks
Sheet1

Cell Formulas
Range	Formula
B19,F19:G19	B19	=SUM(B5:B18)

 

[Muthukrishnan V]

Based on Column B, I want to accomplish Colimns F and G with formula.
Kindly help me with formula for columns F and G.
Thanking you.

 

[Anonymous1378]

SUMIFS and COUNTIFS should work?

Book1
	A	B	C	D	E	F	G
1
2		Excel 365
3		Columns B to G Numericals
4		Amount		From	To	No.of entries	Total Amount
5		401		0	500	4	971
6		718		501	1000	1	718
7		20741		1001	2000	2	3138
8		8700		2001	5000	0	0
9		13		5001	10000	3	26420
10		1459		10001	15000	0	0
11		478		15001	20000	1	15756
12		15756		20001	25000	2	41541
13		20800		25001	50000	1	34007
14		8819		50001		0	0
15		1679
16		8901
17		79
18		34007
19		122551				14	122551
Sheet1

Cell Formulas
Range	Formula
F5:F14	F5	=COUNTIFS($B$5:$B$18,">="&$D5,$B$5:$B$18,"<="&IF($E5<>"",$E5,1E+23))
G5:G14	G5	=SUMIFS($B$5:$B$18,$B$5:$B$18,">="&$D5,$B$5:$B$18,"<="&IF($E5<>"",$E5,1E+23))
B19,F19:G19	B19	=SUM(B5:B18)

 

[Muthukrishnan V]

Thank you Mr Anonymous Sir. Works great. What is 1E+23 in G column formula Sir?

 

[Anonymous1378]

Thank you Mr Anonymous Sir. Works great. What is 1E+23 in G column formula Sir?

It's just scientific notation for 100,000,000,000,000,000,000,000, a really high number which you probably will not be using in the "From" field, as the blank in the "To" field wouldn't work very well.

 

[Muthukrishnan V]

Thank you sir for your kind reply. I am grateful to our forum.