Excel syntax for SUMIF(): |
|---|
=SUMIF(range, criteria[, sum_range]) |
The East Caribbean Dollar, XCD, will be used in D9 to illustrate how SUMIF() can be used to rewrite nested IF()s.
SUMIF() is very similar to SUM(), only it accepts criteria needed to select qualified valued to be added.
Referring to the tables at the top of this post, these are the values needed by SUMIF() in cell D9:
SUMIF() item | Cell(s) or range | Value | Comments |
|---|---|---|---|
range | $G$3:$G$10 | Codes | Column 1 of the Exchange Rates table |
criteria | B10 | XCD | Items in sum_range are added if range item = B9 |
[sum_range] | $H$3:$H$10 | Rates | Range used by SUMIF() for actual calculation |
When range and sum_range are different, sum_range needs to be included. When both are the same, sum_range is optional and can be left out.
The SUMIF() we need looks like this:
B9 acting as a filter for range before using sum_range |
|---|
=SUMIF($G$3:$G$10, B9, $H$3:$H$10) |
Since only 1 item in range equals B9, only 1 item in sum_range qualifies: 0.370194, the rate for XCD.
This excerpt is taken from 8 Ways To Rewrite Nested IF() Functions at Magna Carta XLS Communications. Each of the 8 nested IF() rewrites from that post will be featured in its own post here.
Other posts will be migrated to this blog before I begin writing posts natively here.