Multiply IF

[HARDLYEVER]

I'm trying to multiply percentages if they meet a certain criteria and not having much luck.

The worksheet looks like this.

A	B
88%	Blue
55%	Red
34%	Blue
86%	Green

I want to multiply column A if column B equals Blue.
I tried following some examples found within the forum such as {=PRODUCT(IF(B2:B5="BLUE",A2:A5))} (Ctrl+Shift+Enter) which works. However this worksheet is several thousand rows. When i extend the row criteria to A:A or A2:A300 i get #NA.

Thanks,

 

[CA_Punit]

=PRODUCT(IF(B2:B300="BLUE",A2:A300))

With Ctrl+Shift+Enter (I think you have changed only one criteria A2:A300, there are two criteria)

See if it works

 

[jasonb75]

As @CA_Punit has pointed out, it is likely that you have not changed both ranges in the formula. With a formula like this #N/A! indicates that the arrays are of unequal size.
Perhaps the use of a structured table would prevent errors, this should resize the formula automatically as new data is added. For example

25.08.20.xlsm
	A	B	C	D
1	A	B		29.92%
2	88%	Blue
3	55%	Red
4	34%	Blue
5	86%	Green
Sheet28

Cell Formulas
Range	Formula
D1	D1	=PRODUCT(IF(Table5[B]="BLUE",Table5[A]))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[HARDLYEVER]

=PRODUCT(IF(B2:B300="BLUE",A2:A300))

With Ctrl+Shift+Enter (I think you have changed only one criteria A2:A300, there are two criteria)

See if it works

Thanks @CA_Punit for the suggestion however both criteria were being changed. After looking through all the data, i have a few blank cells and some cells that contain #NA!. Is it possible to add and IFERROR to this formula to overlook the #NA!?

 

[jasonb75]

Perhaps

=PRODUCT(IFERROR((B2:B300="BLUE")*A2:A300,0))

 

[HARDLYEVER]

Perhaps

=PRODUCT(IFERROR((B2:B300="BLUE")*A2:A300,0))

Thanks Jasonb75. Also the suggestion to use structured tables works great.