Multiply IF

HARDLYEVER

New Member
Joined
Aug 25, 2020
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
I'm trying to multiply percentages if they meet a certain criteria and not having much luck.

The worksheet looks like this.

AB
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,
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
=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

Well-known Member
Joined
Dec 30, 2008
Messages
11,900
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1AB29.92%
288%Blue
355%Red
434%Blue
586%Green
Sheet28
Cell Formulas
RangeFormula
D1D1=PRODUCT(IF(Table5[B]="BLUE",Table5[A]))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

HARDLYEVER

New Member
Joined
Aug 25, 2020
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
=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

Well-known Member
Joined
Dec 30, 2008
Messages
11,900
Office Version
  1. 365
Platform
  1. Windows
Perhaps

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

Watch MrExcel Video

Forum statistics

Threads
1,122,303
Messages
5,595,324
Members
413,986
Latest member
Elizsk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top