## SUMPRODUCT – multiple criteria

The formula above in cell G8 uses two conditions in cell G2 and G3 and a date range G5:G6 to create a total. The conditions in cell G2 and G3 are matched to the "Description" values in column C and the date range specified in cell G5 and G6 are used to filter values based on the dates in column B.

### Explaining formula in cell G8

#### Step 1 - Identify values matching cell G2 and G3

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

COUNTIF(G2:G3, C3:C10)

becomes

COUNTIF({"H";"B"},{"A";"H";"B";"C";"H";"F";"E";"E"})

and returns

{0;1;1;0;1;0;0;0}.

This tells us that the second, third and fifth value in column C matches G2 or G3.

The picture below shows the array in column A.

#### Step 2 - Identify values inside date range

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.

(G5<=B3:B10)

returns

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

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

(G6>=B3:B10)

returns

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

#### Step 3 - 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.

#### Step 4 - Sum all numbers in array

The SUMPRODUCT function creates a total based on logical expressions.

SUMPRODUCT(COUNTIF(G2:G3,C3:C10)* (G5<=B3:B10)* (G6>=B3:B10)* D3:D10)

becomes

SUMPRODUCT({0;0;1;0;1;0;0;0}* D3:D10)

Lastly, multiply the array with the amounts in column D.

SUMPRODUCT({0;0;1;0;1;0;0;0}* {50; 30; 100; 100; 80; 90; 70; 30})

calculates to

SUMPRODUCT({0;0;100;0;80;0;0;0})

and returns 180 in cell G8.

### Download Excel *.xlsx

How to do tiered calculations in one formula

The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]

How to use the SUMPRODUCT function

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

SUMPRODUCT and nested IF functions

I have demonstrated in a previous post how to simplify nested IF functions, in this article I will show you how […]

You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. […]

The above image demonstrates how to ignore blank cells in a SUMPRODUCT formula. The following formula is shown in cell E3. […]

### 4 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

Paste image link to your comment.

**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.

please help to generate formula. i need to look if the currency is the same and calculate the exchange rates according to the date range.

Exchange Rates From To EUR GBP USD

Jan 01/01/2019 31/01/2019 1.00 1.055 0.832

Feb 01/02/2019 28/02/2019 1.00 1.097 0.834

Mar 01/03/2019 31/03/2019 1.00 1.094 0.838

Apr 01/04/2019 30/04/2019 1.00 1.113 0.842

May 01/05/2019 31/05/2019 1.00 1.099 0.845

Customer account Invoice Tour id Date Departure date Amount currency VAT amount Net amount Currency Voucher

C000002475 CI17182289 E08205231 01/01/2019 01/01/2019 292.66 0 292.66 EUR CI17182289

C000002475 CI17228471 T02200223 17/01/2019 17/01/2019 136.26 0 136.26 EUR CI17228471

C000002475 CI17231407 T02200224 18/01/2019 18/01/2019 280.32 0 280.32 EUR CI17231407