Two-Dimensional Sumif’s In Excel

Yesterday I needed to perform a two-dimensional sumif’s, and finding syntax on how to do this wasn’t easy (meaning it took more than 3 minutes). So I figured I’d flood the internet with one more example.

My Video below gives a short walk-through of it, but essentially you use SUMPRODUCT function. The syntax is basically SUMPRODUCT((Range1CONDITION=<VALUE>)*(Range2CONDITION=<VALUE>)*DATARANGE).

In my initial example, I use formula:

=SUMPRODUCT((B3:B12=I2)*(C2:F2=I3)*C3:F12)

Order doesn’t matter in the above statements. What the function is actually doing is looping through each cell in the DATARANGE, testing if all the criteria is true. If so, it assigns a 1 in the first two fields, and the test, for each cell in the range, and if true, it assigns a 1 to it.

Sumproduct is also available on Google Drive Sheets, OpenOffice. Not available on Excel 2003 and earlier.

Link to file used in video.