Using an array, I need a formula for the following:

If B2 through B500 = A2
and
If E2 through E500 = "CT" or "NT"
then SUM H2 through H500

Thank you!

Bob

Try this...

=SUMPRODUCT(--(B2:B500=A2),(E2:E500="CT")+(E2:E500="NT"),H2:H500 )

--
Biff
Microsoft Excel MVP


"bob" wrote in message
...
Using an array, I need a formula for the following:

If B2 through B500 = A2
and
If E2 through E500 = "CT" or "NT"
then SUM H2 through H500

Thank you!

Bob

Try this:

=SUMPRODUCT((B2:B500=A2)*(E2:E500={"CT","NT"})*H2: H500)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"bob" wrote in message
...
Using an array, I need a formula for the following:

If B2 through B500 = A2
and
If E2 through E500 = "CT" or "NT"
then SUM H2 through H500

Thank you!

Bob

Thank you for the equation. It works fine. I'd like to add one more IF
variable to the statement: IF J2:J500="W"...but I am not sure where to place
it in the current formula. Also, I am not sure what the function of the two
dashes are (--). Can you please explain?

"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(B2:B500=A2),(E2:E500="CT")+(E2:E500="NT"),H2:H500 )

--
Biff
Microsoft Excel MVP


"bob" wrote in message
...
Using an array, I need a formula for the following:

If B2 through B500 = A2
and
If E2 through E500 = "CT" or "NT"
then SUM H2 through H500

Thank you!

Bob

It's better to use cells to hold the criteria:

A2 = whatever A2 equals!
A3 = CT
A4 = NT
A5 = W

=SUMPRODUCT(--(B2:B500=A2),(E2:E500=A3)+(E2:E500=A4),--(J2:J500=A5),H2:H500)

I am not sure what the function of the two dashes are (--).

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"bob" wrote in message
...
Thank you for the equation. It works fine. I'd like to add one more IF
variable to the statement: IF J2:J500="W"...but I am not sure where to
place
it in the current formula. Also, I am not sure what the function of the
two
dashes are (--). Can you please explain?

"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(B2:B500=A2),(E2:E500="CT")+(E2:E500="NT"),H2:H500 )

--
Biff
Microsoft Excel MVP


"bob" wrote in message
...
Using an array, I need a formula for the following:

If B2 through B500 = A2
and
If E2 through E500 = "CT" or "NT"
then SUM H2 through H500

Thank you!

Bob

"bob" wrote:
I am not sure what the function of the two
dashes are (--). Can you please explain?

It's a simple question to answer.

A single negation ("-") converts a positive number to a negative number.
And vice versa; so a double negation ("--") converts any number, positive or
negative, back to itself.

The purpose of "--" here is to convert the Boolean results (true or false)
from B2:B500=A2 into numbers 0 and 1, which SUMPRODUCT requires.

But there is nothing sacrosant about "--". Including a Boolean expression
in any arithmetic expression will accomplish the same thing; that is,
converting the Boolean result into a number. That is why
(E2:E500="CT")+(E2:E500="NT") is sufficient without the use of "--".


----- original message -----

"bob" wrote in message
...
Thank you for the equation. It works fine. I'd like to add one more IF
variable to the statement: IF J2:J500="W"...but I am not sure where to
place
it in the current formula. Also, I am not sure what the function of the
two
dashes are (--). Can you please explain?

"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(B2:B500=A2),(E2:E500="CT")+(E2:E500="NT"),H2:H500 )

--
Biff
Microsoft Excel MVP


"bob" wrote in message
...
Using an array, I need a formula for the following:

If B2 through B500 = A2
and
If E2 through E500 = "CT" or "NT"
then SUM H2 through H500

Thank you!

Bob