SUMIFS Function In
Excel
The Sumifs Function, One Of The Math And Trig Functions, Adds All Of Its Arguments That Meet Multiple Criteria Means The Microsoft Excel Sumifs Function Return The Addition Of All Numbers In A Range Of Cells, Based On A Single Or Multiple Criteria.
Syntax
=SUMIFS
(sum_range, range1, criteria1, [range2], [criteria2], ...)
Arguments
For Example : As Shown Below In Image, Here You Can See A Table
Whare Store Data In Excel Spreadsheet Related To Transition Of Amount. Now I Want Find Total Addition Of Amount Transaction Type Wise (Debit Card ,Cash
, UPI) This Is Our First Condition And
Second Condition Is Debit Want Separate Column And Credit Amount Want Separate
Column as shown Below Image.
1 Calculate All
UPI Debit Amt In Cell G2
2 Calculate All UPI Credit Amt In Cell H2
3 Calculate All Cash Debit Amt In Cell G3
4 Calculate All Cash Credit Amt In Cell H3
5 Calculate All Debit Card Debit Amt In Cell G4
6 Calculate All Debit Card Credit Amt In Cell G4
1 Calculate All UPI Debit Amt In Cell G2
Formula applied G2
cell which is highlighted by red background and white text color.
Formula =SUMIFS(D2:D13,B2:B13,F2,C2:C13,G1)
2 Calculate All UPI Credit Amt In Cell H2
Formula applied H2
cell which is highlighted by red background and white text color.
Formula : =SUMIFS(D2:D13,B2:B13,F2,C2:C13,H1
3 Calculate All Cash Debit Amt In Cell G3
Formula
applied G3 cell which is highlighted by red background and white text color.
Formula
: =SUMIFS(D2:D13,B2:B13,F3,C2:C13,G1)
4 Calculate All Cash Credit Amt In Cell H3
Formula
applied G3 cell which is highlighted by red background and white text color.
Formula : =SUMIFS(D2:D13,B2:B13,F3,C2:C13,H1)
5 Calculate All Debit Card Debit Amt In Cell G4
Formula
applied G4 cell which is highlighted by red background and white text color.
Formula
: =SUMIFS(D2:D13,B2:B13,F4,C2:C13,G1)
6 Calculate All Debit Card Credit Amt In Cell H4
Formula
applied H4 cell which is highlighted by red background and white text color.
Formula
: =SUMIFS(D2:D13,B2:B13,F4,C2:C13,H1)
Now We have all done
SUMIFS Function Automated Formula Using Mix Reference (F4 Key )
Formula applied G2
cell which is highlighted by red background and white text color.
Formula =SUMIFS($D$2:$D$13,$B$2:$B$13,$F2,$C$2:$C$13,G$1)
After type formula
you have to Drag Right And After it Drag Down
What Is The Difference Between Sumif & Sumifs?
The Sumif Formula
Returns The Sum Of Cells Based On One Criterion (A Result That Matches One
Condition). Whereas, The Sumifs Function Returns The Sum Of Cells That Meet
Multiple Criteria
0 Comments