SUMPRODUCT with multiple OR where looking at 3 columns to then sum another column

[PTHops]

I have the following formula that is just not working with the ORs

=SUMPRODUCT((SSPPI!P2:P23<>"Correct")*OR(SSPPI!P2:P23<>"Correct")*OR(SSPPI!P2:P23<>"Correct")*(SSPPI!I2:I23))

I have the following worksheet with a summary sheet where I am entering the formula.

I want to find the total from Column I if either Columns P, S or V are "Incorrect". I do not want to include values if P, S, or V are "Correct". Basically, exclude any value that is associated with a Completed action.

My existing formula is not totaling correctly. On my example, the total should be $2000 as that is the only one of the 3 columns that is still marked as Incomplete. Instead, I am getting $32,000 which is the total of Rows 2, 3 and 4 (indicating that the OR is not working for Columns S and V.

Any advice is most welcome. Thank you.

 

[awoohaw]

ARe the first three expressions part of an OR statement?
if so, try this (but I don't think it will give you a good result)
but all three of those expressions look to be identical. Are the only possible values "Correct", "Incorrect", or blank?

SUMPRODUCT(
((SSPPI!P2:P23<>"Correct")+(SSPPI!S2:S23<>"Correct")+(SSPPI!V2:V23<>"Correct"))*
(SSPPI!I2:I23)
)

 

[Dave Patton]

Is the following what you require?
Please review the formula. If it is correct, edit the ranges for your data.
The alternatives are all very similar; the syntax is just a little different.
You can copy the post to a clean sheet and review the formulas with Evaluate Formula.

Book1
	D					I							P	Q	R	S	T	U	V
1
2	32					20							Correct			Correct			Incorrect
3	32					12							Incorrect			Correct			Incorrect
4	32					100							Correct			Correct			Correct
5
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(--((P2:P4<>"Correct")+(S2:S4<>"Correct")+(V2:V4<>"Correct")>0),(I2:I4))
D3	D3	=SUMPRODUCT(--((P2:P4<>"Correct")+(S2:S4<>"Correct")+(V2:V4<>"Correct")>0)*(I2:I4))
D4	D4	=SUM(((P2:P4<>"Correct")+(S2:S4<>"Correct")+(V2:V4<>"Correct")>0)*(I2:I4))

 

[Alex Blakenburg]

If I understand it correctly, you want to sum it if
• None of the 3 columns contain "Correct"
• At least 1 column contains "Incorrect"
Just using <> "Correct" means blanks are included.

I am sure someone can make it shorter but does this do what you are after.

20240413 SumProduct Or but Exclude Blank PTHops.xlsx
	F	G	H	I						O	P	Q	R	S	T	U	V
1
2	Formula -->	50		10							Incorrect			Incorrect			Incorrect
3				200							Incorrect			Correct			Incorrect
4				35							Incorrect			Incorrect			Correct
5				40							Incorrect
6				500
7				60							Correct
8				90							Incorrect			Correct
9
SSPPI

Cell Formulas
Range	Formula
G2	G2	=SUMPRODUCT( --( ( (P2:P23="Correct") + (S2:S23="Correct") + (V2:V23="Correct") ) = 0 ) *--( ( (P2:P23="Incorrect") + (S2:S23="Incorrect") + (V2:V23="Incorrect") ) > 0 ) *(I2:I23))

If the formula really is on another sheet then it would looks like this:

=SUMPRODUCT(
                                    --( ( (SSPPI!P2:P23="Correct") + (SSPPI!S2:S23="Correct") + (SSPPI!V2:V23="Correct") ) = 0 )
                                 *--( ( (SSPPI!P2:P23="Incorrect") + (SSPPI!S2:S23="Incorrect") + (SSPPI!V2:V23="Incorrect") ) > 0 )
                                 *(SSPPI!I2:I23))

 

[PTHops]

If I understand it correctly, you want to sum it if
• None of the 3 columns contain "Correct"
• At least 1 column contains "Incorrect"
Just using <> "Correct" means blanks are included.

I am sure someone can make it shorter but does this do what you are after.

20240413 SumProduct Or but Exclude Blank PTHops.xlsx
	F	G	H	I						O	P	Q	R	S	T	U	V
1
2	Formula -->	50		10							Incorrect			Incorrect			Incorrect
3				200							Incorrect			Correct			Incorrect
4				35							Incorrect			Incorrect			Correct
5				40							Incorrect
6				500
7				60							Correct
8				90							Incorrect			Correct
9
SSPPI

Cell Formulas
Range	Formula
G2	G2	=SUMPRODUCT( --( ( (P2:P23="Correct") + (S2:S23="Correct") + (V2:V23="Correct") ) = 0 ) *--( ( (P2:P23="Incorrect") + (S2:S23="Incorrect") + (V2:V23="Incorrect") ) > 0 ) *(I2:I23))

If the formula really is on another sheet then it would looks like this:

=SUMPRODUCT(
                                    --( ( (SSPPI!P2:P23="Correct") + (SSPPI!S2:S23="Correct") + (SSPPI!V2:V23="Correct") ) = 0 )
                                 *--( ( (SSPPI!P2:P23="Incorrect") + (SSPPI!S2:S23="Incorrect") + (SSPPI!V2:V23="Incorrect") ) > 0 )
                                 *(SSPPI!I2:I23))

This works perfectly!! Thank you.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.