Sumproduct
 

Having 'brain freeze' with sumproduct...my formula is:
=SUMPRODUCT(--L2:L306=$L309)--(N2:N306=N$308)
column 'L' is a list of departments (and I can list as either numeric or
alpha)
Column 'N" is dates

What I want is a the number of times the department matches the date in
N$308 (all dates are in date format).

The 'value' error appears to be in the dates.

Sumproduct
 

Corrected structu

=SUMPRODUCT(--(L2:L306=$L309),--(N2:N306=N$308))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ronnomad" wrote:

Having 'brain freeze' with sumproduct...my formula is:
=SUMPRODUCT(--L2:L306=$L309)--(N2:N306=N$308)
column 'L' is a list of departments (and I can list as either numeric or
alpha)
Column 'N" is dates

What I want is a the number of times the department matches the date in
N$308 (all dates are in date format).

The 'value' error appears to be in the dates.

Sumproduct
 

Perhaps you intended to say not
=SUMPRODUCT(--L2:L306=$L309)--(N2:N306=N$308) but
=SUMPRODUCT(--(L2:L306=$L309),--(N2:N306=N$308)) ?

If you are still getting a #VALUE! error after that, you'll need to check
that your dates really are dates, and not text strings.
=ISNUMBER(cellref) and =ISTEXT(cellref) will tell you.
--
David Biddulph

"ronnomad" wrote in message
...
Having 'brain freeze' with sumproduct...my formula is:
=SUMPRODUCT(--L2:L306=$L309)--(N2:N306=N$308)
column 'L' is a list of departments (and I can list as either numeric or
alpha)
Column 'N" is dates

What I want is a the number of times the department matches the date in
N$308 (all dates are in date format).

The 'value' error appears to be in the dates.

Sumproduct
 

Sorry, didn't work. The "value' error is gone but the result is wrong.

More info: there are 14 different departments and a total of 305 lines. I
know the first dept is a total of 9 lines of which 4 should correspond to
the date parameter. The result I get with the new formula is 109. And, when
I look at all 14 depts the total is 1,663. I have a feeling that it is the
way I have the date set up but, since the date is really a number, I'm not
sure that's correct.

Ron R.



"Luke M" wrote:

Corrected structu

=SUMPRODUCT(--(L2:L306=$L309),--(N2:N306=N$308))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ronnomad" wrote:

Having 'brain freeze' with sumproduct...my formula is:
=SUMPRODUCT(--L2:L306=$L309)--(N2:N306=N$308)
column 'L' is a list of departments (and I can list as either numeric or
alpha)
Column 'N" is dates

What I want is a the number of times the department matches the date in
N$308 (all dates are in date format).

The 'value' error appears to be in the dates.