|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 15th, 2009, 05:12 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. 
|
|
#2
June 15th, 2009, 05:38 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
|
#3
June 15th, 2009, 05:58 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
|
#4
June 15th, 2009, 06:04 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
