Conditional calculations — Excel Course

COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF and AVERAGEIFS. Learn it step by step with guided practice on Excelquro.

Understand

Think in three parts: what is being measured, which range contains the condition, and what criteria define the population. The IF version uses one criterion; the IFS version repeats criteria-range pairs. Criteria ranges must line up row-for-row with the sum or average range.

Worked example

The formula adds Collection only where Branch is Mumbai and Product is Loan. Changing either criterion changes the population being summed.

=SUMIFS(E2:E6,A2:A6,"Mumbai",C2:C6,"Loan")

Follow along

1. Use SUM to see total collection first.
2. Replace the unconditional total with SUMIF for one branch.
3. Add Product as the second criterion with SUMIFS.
4. Use COUNTIFS to count cases by Branch and DPD condition.
5. Use AVERAGEIFS to calculate the average collection for a selected branch.
6. Compare the conditional result with the raw rows to validate it.

Interactive practice runs in your browser.