SUMIFS Function In Excel

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


Download Excel Sheet For Practice

Post a Comment

0 Comments