How to Apply COUNTIFS and SUMIFS Formulas in MS Excel

 

We have briefly explained defined to apply SUMIF Formula, COUNTIF Formula and Filter Option in Microsoft excel in our previous articles. However, these formulas are not enough to work as accountant and some queries will create in our mind on setting of above mentioned functions.

For instance, we have extracted of numbers of students and summary of fees amount to be recovered. However, we do not know whether numbers of students in one section of students and how much amount of fees they deposit. We shall apply another functions of Microsoft excel to resolve these query. COUNTIFS Formula and SUMIFS Formula in MS Excel will resolve these queries.

We hereby define COUNTIFS formula and SUMIFS Formula in our succeeding paragraphs. However, create a new table in new worksheet with following headings

Class	Section	No of Students	Fees Amount
6th	A
	B
7th	A
	B
8th	A
	B
9th	A
	B
10th	A
	B
Total

COUNTIFS Formula in Microsoft Excel

We use this formula to count numbers or text fulfilling many conditions in a worksheet or database. Suppose we have 26 students in 6^th class in Section A and Section B. But we want to get figures of both sections then we shall use COUNITIFS Formula giving conditions of class “6^th” and Section “B”. COUNTIFS Formula in MS Excel will be applied as under:-

Place cursor on the cell infront of Section A of 6^th Class in column “No of Students”. Insert formula of COUNTIFS Function as “=COUNTIFS(Students!F2:F121,"6th",Students!G2:G121,"A")”. Definition of mentioned formula is as under:-

=COUNTIFS.      COUNTIFS is the function or formulas through we have to count students of both sections of 6^th class.

Students! This phrase Shows the name of worksheet where students of all class given in workbook.

F2:F121 is the range of students’ sheet where classes of all students are given.

6th is class of students, we have given first conditions to formula which means that formula has to count only students of sixth class.

Similarly, Students!G2:G121, is the range of students in worksheet, where sections of each class is given students worksheet.

“A” is 2^nd condition of formula, which defines that count students of 6^th only in Section “A”.

To count students of Section B in 6^th class, edit and select whole formula of Section A students of sixth class and press Ctrl + C to copy the formula. Then press Ctrl+V to paste it on the cell infront Section B. Edit pasted formula. Replace A in second and type B, which count students of Section B in Class 6th.

Accordingly paste same formula in next class and next section and change both required conditions which will give the desired results of all classes.

SUMIFS Formulas in MS Excel

Like MS COUNTIFS Formulas, SUMIFS Formulas in Microsoft Excel facilitates us to sum the amounts by giving more than one criteria. SUMIFS formulas can be applied on digits however; conditions can be given in text, digits or any other type. Follow following method to insert COUNITIFS function in MS Excel

Place cursor in the cell in Fee amount infornt of 6^th class row and insert formula; =SUMIFS(Students!H2:H121,Students!F2:F121,"6th",Students!G2:G121,"A"). Which will give you desired results.

Definition of Formula

=SUMIFS is Formula or function in MS Excel which can be applied to sum up amount in a worksheet for many criteria or conditions.

Students!H:H121 is the range of students worksheet, where fees amount of all students is given. This function will pick up the amount from this range for calculation as per criteria given.

Students!F2:F121,"6th" is the range of students worksheet where classes of students have been shown against each student. This means that we have given first criteria in this formula to sum the amount of student in class sixth only.

Students!G2:G121,"A" is the range students worksheet whereas section of all students have been given. This means that we have given second criteria to SUMIFS function to sum the amount of students in section A.

As a whole definition, we have inserted formulas to calculate the amount of students of sixth studying in Section A.

To apply the formulas on next sections and classes of school, press F2 to edit the formula and select all formula and paste in next cell. Then change the criteria of class or section as per need, which will give desired output.

In the Last Total Row, apply simple AutoSum Formula to get overall sums of students.  Finally following formulas will be set for each cell of summary.

Class	Section	Numbers	Fees Amount
6th	A	=COUNTIFS(Students!F2:F121,"6th", Students!G2:G121,"A")	=SUMIFS(Students!H2:H121,Students!F2:F121,"6th",Students!G2:G121,"A")
	B	=COUNTIFS(Students!F2:F121,"6th",Students!G2:G121,"B")	=SUMIFS(Students!H2:H121,Students!F2:F121,"6th",Students!G2:G121,"B")
7th	A	=COUNTIFS(Students!F2:F121,"7th",Students!G2:G121,"A")	=SUMIFS(Students!H2:H121,Students!F2:F121,"7th",Students!G2:G121,"A")
	B	=COUNTIFS(Students!F2:F121,"7th",Students!G2:G121,"B")	=SUMIFS(Students!H2:H121,Students!F2:F121,"7th",Students!G2:G121,"B")
8th	A	=COUNTIFS(Students!F2:F121,"8th",Students!G2:G121,"A")	=SUMIFS(Students!H2:H121,Students!F2:F121,"8th",Students!G2:G121,"A")
	B	=COUNTIFS(Students!F2:F121,"8th",Students!G2:G121,"B")	=SUMIFS(Students!H2:H121,Students!F2:F121,"8th",Students!G2:G121,"B")
9th	A	=COUNTIFS(Students!F2:F121,"9th",Students!G2:G121,"A")	=SUMIFS(Students!H2:H121,Students!F2:F121,"9th",Students!G2:G121,"A")
	B	=COUNTIFS(Students!F2:F121,"9th",Students!G2:G121,"B")	=SUMIFS(Students!H2:H121,Students!F2:F121,"9th",Students!G2:G121,"B")
10th	A	=COUNTIFS(Students!F2:F121,"10th",Students!G2:G121,"A")	=SUMIFS(Students!H2:H121,Students!F2:F121,"10th",Students!G2:G121,"A")
	B	=COUNTIFS(Students!F2:F121,"10th",Students!G2:G121,"B")	=SUMIFS(Students!H2:H121,Students!F2:F121,"10th",Students!G2:G121,"B")
Total		=SUM(G2:G11)	=SUM(H2:H11)

See video of COUNTIFS and SUMSIFS placed below uploaded on our YouTube Channel for your better understanding