The "Or" operator

[sherifhanna82]

Hello everyone,

I am trying to use the Count & Sum if functions, if the criteria range contains one or more value from a set of strings.

Example:

I want to sum the numbers in the A column, if the any of the cells in the B column contain X or Y or Z. So, it would look something like this:

=sumif(B:B,"x" ? "y" ? "z",A:A). I want the formula to return the sum of the cells adjacent to those who have X, Y, or Z, but not necessarily all of them together. The ? sign indicates the operator/sign I am missing.

Thanks for your input.

Sherif.

 

[Haseeb Avarakkan]

Hello, Welcome to MrExcel. Try

=SUM(SUMIF(B:B,{"X","Y","Z"},A:A))

 

[Aladin Akyurek]

Hello everyone,

I am trying to use the Count & Sum if functions, if the criteria range contains one or more value from a set of strings.

Example:

I want to sum the numbers in the A column, if the any of the cells in the B column contain X or Y or Z. So, it would look something like this:

=sumif(B:B,"x" ? "y" ? "z",A:A). I want the formula to return the sum of the cells adjacent to those who have X, Y, or Z, but not necessarily all of them together. The ? sign indicates the operator/sign I am missing.

Thanks for your input.

Sherif.

Try...

=SUM(SUMIF(B:B,{"x","y","z"},A:A))

If you would enter x, y, and z in say E2:E4...

=SUMPRODUCT(SUMIF(B:B,E2:E4,A:A))

 

[AlphaFrog]

deleted

 

[AlphaFrog]

but not necessarily all of them together.

If a cell in column B could have any combination of X, Y and\or Z, but you only want to sum the corresponding value in A once, then try something like this...

Excel Workbook
	A	B	C
1	1	x	20
2	2
3	3
4	4	xy
5	5
6	6
7	7	xz
8	8	yz
9	9
10	10
Sheet