## SUMPRODUCT – multiple criteria

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

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

Running totals within date range

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]

Dynamic team generator in excel

Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]

Find empty cells and sum cells above

Is it possible to quickly select all empty cells and then sum cells above to next empty cell? Can I have […]

How to use the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]

The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data. […]

### 3 Responses to “SUMPRODUCT – multiple criteria”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

I don't understand why do you say „Use asterisks between logical expressions instead of commas and you don't need to enter the SUMPRODUCT function as an array formula.”

Formula

`=SUMPRODUCT(COUNTIF(G2:G3,C3:C10),--(G5=B3:B10),D3:D10)`

works fine and isn't an array formula...something is wrong with autoformatting.

The formula is:

SUMPRODUCT(COUNTIF(G2:G3,C3:C10),--(G5=B3:B10),D3:D10)

Ciprian Stoian

I believe SUMPRODUCT in earlier Excel versions had to be entered as an array formula in some cases in order to work. The asterisk made it possible to enter the formula as a regular formula.

This seems to have changed in Excel 365, thanks for pointing it out.