Can't seem to get my Array formula to work................

[Dan the Man]

The Design

I have the array formula below which is designed to capture all data entries
between a specific time period (2007), look only at entires entitled "CIC
(column c reference), and further look only at those 2007 CIC entires that
show an "improvement" score (scores greater than 0). The improvement scores
are identified in column k.

The Formula

=SUMPRODUCT(--('Q 3 + 7'!A4:A500>=DATE(2007,1,1)),--('Q 3 +
7'!A4:A500<=DATE(2007,12,31)),--('Q 3 + 7'!C4:C500="CIC"),--('Q 3 +
7'!K4:K500>0))

The Problem

I keep getting an outcome from this formula of "0", and I know that isn't
accurate. On my sheet entitled "Q3+7" I have 112 clients who show improvement
on column K, so I know that "0" isn't the accurate answer. That means I've
made a formula error I suspect.

Thanks in advance for any suggestions. I'm sure it is a minor "tweak", but I
just can't figure it out....Dan

 

[Barb Reinhardt]

Are you sure all of the dates you are testing really are dates? Try this

Enter
ISTEXT(A1) where A1 is a cell reference for a date field. If you get TRUE,
they are text, not dates. You can fix that by using TEXT TO COLUMNS and
formatting the column as a date. If you need more assistance, come back and
someone should be able to help.

 

[Mike H]

Hi,

While Sumproduct is an array it doesn't have to be array enter. having said
that your formula works fine for me so be suspicious of your dates, are they
really dates or text that look like dates. Likewise your numbers in Column K

with CIC check for rogue spaces

Mike

 

[Sheeloo]

I think you need to put an IF around all comparisons like ('Q 3 +
7'!A4:A500>=DATE(2007,1,1)) like given below;
IF('Q 3 + 7'!A4:A500>=DATE(2007,1,1),1,0)

I checked with the following;
=SUMPRODUCT(IF((A23:A26>=DATE(2007,1,1)),1,0),IF((B23:B26>=DATE(2007,1,1)),1,0))

where I put From dates in A23:26, To dates in B23:26...

I did that and got the expected result.

The way you have written multiplies TRUE with TRUE or FALSE. I am not sure
how Excel handles this.