Help with a complicated array formula

B

boooney

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 where:
(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.
 
M

mikebres

Try this
{=SUM(IF(((dataA=1)+(dataA=2)+(dataA=3))*((dataB>4)*(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
 
B

Biff

Hi!

This is very confusing (at least it is to me):
(column B >4 and <5)
Click to expand...
1-3 are numbers and 4,5 are dates
Click to expand...

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
 
D

Domenic

Try...

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

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

Hope this helps!
 
M

mikebres

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 ?
 
S

SteveG

This seemed to work changing the original suggestion posted slightly.

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

The DATE is your start and end dates.


Cheers,

Steve
 
Top