# Multi-condition sumif problem

#### tjak

##### Board Regular
I need to total column T IF column x="valid" AND column v="yes". I searched the forum and found:

=sumproduct(--(x3:x3000="valid"),--(v3:v3000="yes"),t3:t3000)

(I've put my columns in obviously.)

However the result looked familiar, so I tried my previous formula:

=sumif(x3:x3000,"valid",t3:t3000) and got the same result, even though I have rows with "valid" but without "yes". I need a total if BOTH conditions are true - what did I do wrong here?

And thanks for your support!

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Superstar31

##### Active Member
I need to total column T IF column x="valid" AND column v="yes". I searched the forum and found:

=sumproduct(--(x3:x3000="valid"),--(v3:v3000="yes"),t3:t3000)

(I've put my columns in obviously.)

However the result looked familiar, so I tried my previous formula:

=sumif(x3:x3000,"valid",t3:t3000) and got the same result, even though I have rows with "valid" but without "yes". I need a total if BOTH conditions are true - what did I do wrong here?

And thanks for your support!

Your forumula works for me?? I think looking atyour sumif, its only looking for the "valid" comment, so thats throwing you off,

#### tjak

##### Board Regular
Superstar, I'm not sure I understood your reply. The two formulas should NOT give the same result. The sumproduct is supposed to use 2 criteria and the sumif is only using 1 criteria.

As my data currently sits, the sumproduct total should be less than the sumif total because I have rows which do not meet both criteria in the sumproduct formula.

Is there a structural problem with the sumproduct formula? Or is there another / better way to do this?

#### Aladin Akyurek

##### MrExcel MVP
Superstar, I'm not sure I understood your reply. The two formulas should NOT give the same result. The sumproduct is supposed to use 2 criteria and the sumif is only using 1 criteria.

As my data currently sits, the sumproduct total should be less than the sumif total because I have rows which do not meet both criteria in the sumproduct formula.

Is there a structural problem with the sumproduct formula? Or is there another / better way to do this?

Are the values in V3:V3000 manually entered or calculated by a formula? If latter, what is the formula that has been used?

#### tjak

##### Board Regular

ADVERTISEMENT

Column v is from the formula:

If(and(i3="y",p3="y"),"YES"," ") copied down entire column

Column x "VALID" is manually entered.

#### Superstar31

##### Active Member
Book1
ABCDE
3ValidYES3
4ValidNO5
5MaybeMaybe7
6NotValidYES98
7ValidNO325387
8ValidMaybe423
9ValidYES423
10MaybeNO46
11NotValidMaybe45
12ValidYES645
13ValidNO75611886
14ValidMaybe856
15MaybeYES7
16NotValidNO564
17ValidMaybe455
18ValidYES3425
19ValidNO3245
20MaybeMaybe345
21NotValidYES34
22ValidNO645
23ValidMaybe76
24ValidYES456
25MaybeNO4356
26NotValidMaybe4356
27ValidYES435
28ValidNO6
Sheet1

#### Superstar31

##### Active Member

ADVERTISEMENT

I even tried with with your forumlua, it works just fine on a blank worksheet

#### Aladin Akyurek

##### MrExcel MVP
Column v is from the formula:

If(and(i3="y",p3="y"),"YES"," ") copied down entire column

Column x "VALID" is manually entered.

Try to avoid " " (a space). Better: ""

BTW, the entries do not appear to be the source of the unexpected results of the SumProduct formula which fits the multiconditional summing task.

Try to run ASAP Utilities to clean up any stray spaces in X3:X3000...

#### Aladin Akyurek

##### MrExcel MVP
Column v is from the formula:

If(and(i3="y",p3="y"),"YES"," ") copied down entire column

Column x "VALID" is manually entered.

Try to avoid " " (a space). Better: ""

BTW, the entries do not appear to be the source of the unexpected results of the SumProduct formula which fits the multiconditional summing task.

Try to run ASAP Utilities to clean up any stray spaces in X3:X3000...

Replies
3
Views
99
Replies
2
Views
484
Replies
8
Views
302
Replies
4
Views
1K
Replies
1
Views
302

Threads
1,140,939
Messages
5,703,280
Members
421,289
Latest member
fbohlandt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back