SUMFIF Question
Posted by Andonny on December 15, 2000 2:51 PM
Hi,
I would love to know if the following is possible:
I am using this formula at the moment =SUMIF(A3:B22,"=b",C3:C22) and I would like to not limit to "=b". If I could do something like =SUMIF(A3:B22,"=b or =c or =a",C3:C22) would be great.
Thanks for your help
Andonny
Posted by Tim Francis-Wright on December 15, 2000 4:42 PM
Use this array formula;
(use control-shirt-enter, not just enter, to
enter it):
=SUM(IF((A3:B22="a")+(A3:B22="b")+(A3:B22="c"),1)*C3:C22)
This will work if the value in either column
A or B is a, b, or c--the regular SUMIF
function assumes that the number of columns
in the first block (in your case, 2) will
correspond to the same number of columns in the
second block--so watch out.
Good luck!
Posted by Aladin Akyurek on December 16, 2000 5:24 AM
Tim,
If the data look like
a,8,1,1
a,90,1,1
5,a,1,1
b,b,1,1
34,b,1,1
b,b,1,1
a,a,1,1
a,c,1,1
8,c,1,1
-,8,1,-
a,4,1,1
b,-,1,1
a,a,1,1
a,c,1,1
a,b,1,1
-,-,-,-
45,7,1-
23,5,1,-
12,a,1,1
b,c,1,1
where the first 3 columns represent the possible values in Andonny's range A3:C22.
The values in D3:D22 are generated with
=IF(OR(A3="a",A3="b",A3="c",B3="a",B3="b",B3="c"),C3,"").
By the way "-" means blank.
I get by your array formula 24 as result. Summing up the values in D, on the other hand, results in 16 and summing up the values in C in 19.
What am I missing here?
Aladin
