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
0 Comments
Please do not enter any spam link in the comments box