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!
 

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

Forum statistics

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

Which adblocker are you using?

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
Top