Multi-condition sumif problem

tjak

Board Regular
Joined
Jan 2, 2003
Messages
117
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!
 

Some videos you may like

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
Joined
Nov 10, 2005
Messages
496
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
Joined
Jan 2, 2003
Messages
117
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
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Jan 2, 2003
Messages
117

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
Joined
Nov 10, 2005
Messages
496
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
Joined
Nov 10, 2005
Messages
496

ADVERTISEMENT

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Feb 14, 2002
Messages
85,201
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...
 

Watch MrExcel Video

Forum statistics

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