Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF Multiple Criteria in different ranges. | Excel Worksheet Functions | |||
Sumif with 2 ranges & 2 criteria | Excel Worksheet Functions | |||
Sumproduct using named ranges and multiple criteria | Excel Discussion (Misc queries) | |||
Calculate a SUMIF if criteria is between 2 date ranges | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) |