Multi-condition sumif problem

[tjak]

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!

 

[Superstar31]

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]

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]

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]

Column v is from the formula:

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

Column x "VALID" is manually entered.

 

[Superstar31]

Book1
	A	B	C	D	E
3	Valid	YES	3
4	Valid	NO	5
5	Maybe	Maybe	7
6	NotValid	YES	98
7	Valid	NO	32		5387
8	Valid	Maybe	423
9	Valid	YES	423
10	Maybe	NO	46
11	NotValid	Maybe	45
12	Valid	YES	645
13	Valid	NO	756		11886
14	Valid	Maybe	856
15	Maybe	YES	7
16	NotValid	NO	564
17	Valid	Maybe	455
18	Valid	YES	3425
19	Valid	NO	3245
20	Maybe	Maybe	345
21	NotValid	YES	34
22	Valid	NO	645
23	Valid	Maybe	76
24	Valid	YES	456
25	Maybe	NO	4356
26	NotValid	Maybe	4356
27	Valid	YES	435
28	Valid	NO	6
Sheet1

 

[Superstar31]

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

 

[Aladin Akyurek]

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]

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