Consider this worksheet fragment:

The formula =SUMIF(A2:A5,"Yes",B2:B5) will add up all the values in B2:B5 for which the corresponding entry in A2:A5 is Yes and it will return 550 (350+200).

COUNTIF works similarly and counts how many matching entries there are. It would read =COUNTIF(A2:A5,"Yes") and would return 2.

But how do you handle the situation where you want to test for more than one condition. For example, if you want to know how much you've paid to Staff in the Accounts department? SUMIF doesn't handle multiple criteria so you can't use it.

To do this you can use an array formula. An Array formula is a more complex formula to create but it works very well in situations like this one. The array formula which will solve our problem is:

=SUM(IF((A2:A5="Yes")*(C2:C5="Accounts"),B2:B5))

or even shorter, =SUM((A2:A5="Yes")*(C2:C5="Accounts")*B2:B5)

These formulas only add the corresponding entry in column B if the corresponding tests on the data in columns A and C return true values. Excel calculates this by treating the values in columns A, B & C as one dimensional arrays and it performs the calculations using these arrays. It evaluates the tests on the data in row 2, then row 3, then row 4 and finally row 5 and then sums the results.

To count the matching entries (rather than sum them) use:

=COUNT(IF((A2:A5="Yes")*(C2:C5="Accounts"),B2:B5))

However, you can't just enter either of these formulas into a cell and expect it to work - it won't unless you press Control + Shift + Enter which indicates to Excel that you're using an Array formula. If you forget, you'll see #VALUE! appear in the cell and you should select the cell, press F2 as if to edit it and press Control + Shift + Enter to enter the formula in the correct manner.

Array formulas like these make multiple calculations and return a single result from them. While many calculations can only be made using array formulas, this is one calculation you could have made another way -- using Excel's Conditional Sum Wizard add-in.

Test this by loading the Add-in -- choose Tools, Add-ins, enable the Conditional Sum Wizard and click Ok. You'll find it is now installed on the Tools menu - select Wizard then Conditional Sum. To create the formula, first select the entire data area as the list to work with, then specify the two tests which must be met (the Staff value must be equal to Yes and the Department value equal to Accounts, and finally indicate the cell to contain the answer. The resulting formula will be different to the two we've shown but works just as well.