Sumproduct

[ronnomad]

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.

 

[Luke M]

Corrected structure:

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

 

[David Biddulph]

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.

 

[ronnomad]

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.