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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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

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?

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

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

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

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

##### 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
2
Views
159
Replies
1
Views
120
Replies
2
Views
90
Replies
2
Views
40
Replies
10
Views
157

1,113,861
Messages
5,544,721
Members
410,630
Latest member
JFORTH97