## SUMPRODUCT – multiple criteria

*Article updated on December 07, 2017*

The formula above in cell G8 uses two conditions in cell G2 and G3 and a date range G5:G6 to build a total.

Note, this is a regular formula. Use asterisks between logical expressions instead of commas and you don't need to enter the SUMPRODUCT function as an array formula.

The COUNTIF function allows you to compare multiple values to one column.

becomes

and returns {0;1;1;0;1;0;0;0}. This tells us that the second, third and fifth value in column C matches either G2 or G3.

The picture below shows the array in column A.

To match a date range we need two logical expressions, the first one checks if any of the dates in B3:B10 are larger than or equal to the start date in G5.

returns {TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE}.

The second logical expression matches dates smaller than or equal to the end date.

returns {FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE}

### Apply AND logic to all arrays

Column D shows the result of multiplying all arrays from the logical expressions and the COUNTIF function. This is the same as AND logic for values row wise.

For example, the first value on row 3 is 0 (zero), the next is TRUE and the last value is FALSE. 0*TRUE*FALSE equals FALSE. All values must be TRUE to return TRUE.

Note, 0 (zero) is the same as FALSE and 1 is TRUE in Excel. Row 5 and 7 in column D return 1.

### Sum all values

The formula is now:

Lastly, multiply the resulting array with the amount values in column D.

calculates to

and returns 180 in cell G8.

### Download Excel *.xlsx

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article