Help with a complicated array formula
 

Hi--

I'm trying to sum an array that meets certain conditions into a single
cell

A B C
1
2
3

I need the function to sum column C for instances whe
(column A = 1 or 2 or 3) and
(column B 4 and <5)

1-3 are numbers and 4,5 are dates

Can anyone help with the syntax I should use? I've found array formula
samples of AND and OR, but not both together. Everything I've tried so
far has failed to produce the result I need.

Thanks in advance.

Help with a complicated array formula
 

Try this
{=SUM(IF(((dataA=1)+(dataA=2)+(dataA=3))*((dataB4 )*(dataB<5)),dataC))}

Make sure you press Control Shift Enter to make this an array formula. Just
to simplfy the formula I named the ranges where dataA is the data in column
A, dataB is the data in column B, etc. To explain a little, the plus works
as an OR and the multilpication acts as an AND.

Mike

"boooney" wrote:

Hi--

I'm trying to sum an array that meets certain conditions into a single
cell

A B C
1
2
3

I need the function to sum column C for instances whe
(column A = 1 or 2 or 3) and
(column B 4 and <5)

1-3 are numbers and 4,5 are dates

Can anyone help with the syntax I should use? I've found array formula
samples of AND and OR, but not both together. Everything I've tried so
far has failed to produce the result I need.

Thanks in advance.

Help with a complicated array formula
 

Hi!

This is very confusing (at least it is to me):

(column B 4 and <5)

1-3 are numbers and 4,5 are dates

If 4 and 5 are dates like 12/4/2005, 12/5/2005, then there is no date that
is 4 AND <5.

Or am I just having one of those days?

Biff

"boooney" wrote in message
oups.com...
Hi--

I'm trying to sum an array that meets certain conditions into a single
cell

A B C
1
2
3

I need the function to sum column C for instances whe
(column A = 1 or 2 or 3) and
(column B 4 and <5)

1-3 are numbers and 4,5 are dates

Can anyone help with the syntax I should use? I've found array formula
samples of AND and OR, but not both together. Everything I've tried so
far has failed to produce the result I need.

Thanks in advance.

Help with a complicated array formula
 

Try...

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{1,2,3},0))),--(B1:B10D1),--(B1:B10
<E1),C1:C10)

....where D1 contains your start date, and E1 contains your end date.

Hope this helps!

In article .com,
"boooney" wrote:

Hi--

I'm trying to sum an array that meets certain conditions into a single
cell

A B C
1
2
3

I need the function to sum column C for instances whe
(column A = 1 or 2 or 3) and
(column B 4 and <5)

1-3 are numbers and 4,5 are dates

Can anyone help with the syntax I should use? I've found array formula
samples of AND and OR, but not both together. Everything I've tried so
far has failed to produce the result I need.

Thanks in advance.

Help with a complicated array formula
 

If I understood him correctly then:
A B C
1 3.6 1
2 4.6 3
3 4.2 5
4 12/13/2005 ?
5 11/12/2005 ?

"Biff" wrote:

Hi!

This is very confusing (at least it is to me):

(column B 4 and <5)

1-3 are numbers and 4,5 are dates

If 4 and 5 are dates like 12/4/2005, 12/5/2005, then there is no date that
is 4 AND <5.

Or am I just having one of those days?

Biff

"boooney" wrote in message
oups.com...
Hi--

I'm trying to sum an array that meets certain conditions into a single
cell

A B C
1
2
3

I need the function to sum column C for instances whe
(column A = 1 or 2 or 3) and
(column B 4 and <5)

1-3 are numbers and 4,5 are dates

Can anyone help with the syntax I should use? I've found array formula
samples of AND and OR, but not both together. Everything I've tried so
far has failed to produce the result I need.

Thanks in advance.

Help with a complicated array formula
 

This seemed to work changing the original suggestion posted slightly.

{=SUM(IF((A1:A4={1,2,3})*((B1:B4DATE(2005,12,1))* (B1:B4<DATE(2005,12,31))),C1:C4))}

The DATE is your start and end dates.


Cheers,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=492284

Help with a complicated array formula
 

Thanks Guys. I will try each of these out and let you know.

Dan

Help with a complicated array formula
 

At least a couple of these worked beautifully!! Thanks for your help.

Dan