Sum If Multiple Ifs

[blarrabee]

Trying to sum numbers under the following conditions:


Sum If

2009!Q is greater than B and less than C

and 2009!I = A

any help would be appreciated.

Thanks!

 

[T. Valko]

perfect. thanks. forgot about the ampersands.

You're welcome. Thanks for the feedback!

 

[CVSoprano]

=SUMPRODUCT(--(2009!Q1:Q100>B1:B100),--(2009!Q1:Q100 < C1:C100),--(2009!I1:I100="A"))

I have a related question, and this example is a good one to work with. I've been using SUMPRODUCT off and on over the years, but am failing to remember one trick:

What if, in the example above, I want the last condition to be equal to "A" OR "B"? What is the syntax?

 

[T. Valko]

I have a related question, and this example is a good one to work with. I've been using SUMPRODUCT off and on over the years, but am failing to remember one trick:

What if, in the example above, I want the last condition to be equal to "A" OR "B"? What is the syntax?

A couple of ways...


Best method:

Use cells to hold the criteria:

• A1 = A
• A2 = B

Then:

=SUMPRODUCT(--(Q1:Q100>B1:B100),--(Q1:Q100 < C1:C100),--(ISNUMBER(MATCH(I1:I100,A1:A2,0))))

Or, like this:

=SUMPRODUCT((Q1:Q100>B1:B100)*(Q1:Q100 < C1:C100)*(I1:I100={"A","B"}))

 

[CVSoprano]

Yep, that occurred to me about two minutes before you posted. Thanks for the response!

 

[T. Valko]

Yep, that occurred to me about two minutes before you posted. Thanks for the response!

You're welcome!