CODEWITHSUNDEEP
excelif-statementexcel-formulaproduct
Adam Durrant
Adam Durrant

Reputation: 11

PRODUCT IF - Multiple Criteria

I'm looking to multiple some numbers together if the numbers are within a certain time period e.g 2002 - 2008. I can create a PRODUCT(IF( function that will allow me to do this for a single criteria, however when I try using PRODUCT(IF(AND( with 2 criteria, then the product returns 0. Please see below for examples - I've picked a trivial second criteria which will obviously hold:

2001    1.018
2002    1.015
2003    1.031
2004    1.025
2005    1.032
2006    1.026
2007    1.045
2008    1.042
2009    1.000
2010    1.050

{=PRODUCT(IF(A1:A10>=2003,B1:B10))} = 1.279331

{=PRODUCT(IF(AND(A1:A10>=2003,A1:A10>=2002),B1:B10))} = 0

These answers should logically produce the same result as the second criteria trivially holds.

Any thoughts?

Upvotes: 0

Views: 7665

Answers (2)

Sergio Pennock
Sergio Pennock

Reputation: 1

I assume that you are looking for the data in the range >=2002 and <=2003. Try the array formula:

=PRODUCT(IF(((A1:A10)<=2003)*((A1:A10)>=2002),B1:B10))

Using CTRL+SHIFT+ENTER instead of ENTER to confirm.

Upvotes: 0

user4039065
user4039065

Reputation:

Try this array formula,

=PRODUCT(IF(A1:A10>=2003, if(A1:A10>=2002, B1:B10)))

Array formulas don't like AND or OR since they are a form of array processing already.

I hope your actual example is something else since the logic here is superfluous.

Upvotes: 2

Related Questions