How to make Array Formula with "or" condition

[jasontoh]

Hi,

Can someone help me: how to make Array Formula in cell B11 with condition "A or B & Y"

Thanks
Jason

===================================================

Excel Workbook
	A	B	C
1	Condition 1	Condition 2	Data
2	A	Y	0.20
3	C	Y	0.45
4	B	N	1.10
5	B	Y	12.00
6	A	N	9.40
7	A	N	0.01
8	*	*	*
9	*	Y	*
10	A	0.20	*
11	A or B	?	*
Sheet1

 

[Ron Coderre]

Actually you don't need to use "array formulas"
You can replace the B10 formula with this:

=SUMPRODUCT((A2:A7=A10)*(B2:B7=B9)*C2:C7)

and use this for the "or" formula

B11: =SUMPRODUCT((A2:A7={"A","B"})*(B2:B7=B9)*C2:C7)

Is that something you can work with?

 

[jasontoh]

Hi Ron,

Thanks for the quick & interesting solution
But my issue is that column C contain formula to display blank cell & it does not work with sumproduct in case it become blank
I change my sample table to show this issue
Will be glad if there is another way out

Jason
===============================================

Excel Workbook
	A	B	C	D	E
1	Condition 1	Condition 2	Result	Data	Rate
2	A	O	0.20	0.20	1.0
3	C	O	0.68	0.45	1.5
4	B	*	*	1.10	1.0
5	B	O	12.00	12.00	1.0
6	A	*	*	9.40	2.0
7	A	O	0.02	0.01	1.5
8	C	*	*	0.45	1.0
9	A	*	*	0.22	1.0
10	*	*	*	*	*
11	*	O	O	*	*
12	A	#VALUE!	0.22	*	*
13	A or B	#VALUE!	?	*	*
14	*	*	*	*	*
15	*	sumproduct	CSE	*	*
Sheet1

 

[T. Valko]

Hi Ron,

Thanks for the quick & interesting solution
But my issue is that column C contain formula to display blank cell & it does not work with sumproduct in case it become blank
I change my sample table to show this issue
Will be glad if there is another way out

Jason
===============================================

Can someone help me: how to make Array Formula in cell B11 with condition "A or B & Y"

Try this...

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A7,{"A","B"},0))),--(B2:B7="Y"),C2:C7)

 

[jasontoh]

Hi Biff,

That seems to have done it!
Thanks for your help

Jason

 

[T. Valko]

Hi Biff,

That seems to have done it!
Thanks for your help

Jason

You're welcome. Thanks for the feedback!