Awkward sumif/sumproduct with three criteria over two ranges
 

need to sumif or sumproduct based on text values in two columns:

so:

sum values in sumrange if col1 value= "x" AND col2 value = ("y" OR "z")

please advise

Awkward sumif/sumproduct with three criteria over two ranges
 

Something like this should work:
=SUMPRODUCT((B2:B4="x")*((C2:C4="y")+(C2:C4="z")), A2:A4)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PBcorn" wrote:
need to sumif or sumproduct based on text values in two columns:
sum values in sumrange if col1 value= "x" AND col2 value = ("y" OR "z")

Awkward sumif/sumproduct with three criteria over two ranges
 

Say in cols A thru C:

1 q q
2 q q
3 q q
4 q q
5 x q
6 x y
7 x y
8 x y
9 x q
10 x q
11 x z
12 x z
13 x z
14 x q
15 x q
16 q q
17 q q
18 q q
19 q q
20 q q

and we want to sum col A for col B="x" and col C="y" or "z"

=SUMPRODUCT(A1:A20,--(B1:B20="x"),--(C1:C20="y"))+SUMPRODUCT(A1:A20,--(B1:B20="x"),--(C1:C20="z"))

will give 57
--
Gary''s Student - gsnu200791


"PBcorn" wrote:

need to sumif or sumproduct based on text values in two columns:

so:

sum values in sumrange if col1 value= "x" AND col2 value = ("y" OR "z")

please advise

Awkward sumif/sumproduct with three criteria over two ranges
 

OK,

now I have even more criteria to add:

sum values in sumrange if col1 = "x" OR"y" OR "z" AND col2 = "p" OR "q"

Please advise

"PBcorn" wrote:

need to sumif or sumproduct based on text values in two columns:

so:

sum values in sumrange if col1 value= "x" AND col2 value = ("y" OR "z")

please advise

Awkward sumif/sumproduct with three criteria over two ranges
 

sum values in sumrange if col1 = "x" OR"y" OR "z" AND col2 = "p" OR "q"

Something like this:
=SUMPRODUCT(((B2:B4="x")+(B2:B4="y")+(B2:B4="z"))* ((C2:C4="p")+(C2:C4="q")),A2:A4)

Do press the "Yes" buttons (like the one below) for responses which answer
your questions
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---