| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct by year??
I have a multi-columned worksheet, and am trying to pick out certain data.
One column has file names & the other has dates this file was submitted. The formulas I'm using a =SUMPRODUCT(--($C$2:$C$15403<"ABCD*"),(--(YEAR($I$2:$I$15403)=2007))) where "ABCD*" is the prefix for the file name. This seems to be ok, except I'm gett ALL of the files that were filed in 2007, not just the ones with the ABCD prefix. Any help is appreciated... Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct by year??
I think you need LEFT and don't you want = not <
=SUMPRODUCT(--(left($C$2:$C$15403,4)="ABCD"),(--(YEAR($I$2:$I$15403)=2007))) "MDI Anne" wrote: I have a multi-columned worksheet, and am trying to pick out certain data. One column has file names & the other has dates this file was submitted. The formulas I'm using a =SUMPRODUCT(--($C$2:$C$15403<"ABCD*"),(--(YEAR($I$2:$I$15403)=2007))) where "ABCD*" is the prefix for the file name. This seems to be ok, except I'm gett ALL of the files that were filed in 2007, not just the ones with the ABCD prefix. Any help is appreciated... Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct by year??
Perfect!! Thank you!!
"Joel" wrote: I think you need LEFT and don't you want = not < =SUMPRODUCT(--(left($C$2:$C$15403,4)="ABCD"),(--(YEAR($I$2:$I$15403)=2007))) "MDI Anne" wrote: I have a multi-columned worksheet, and am trying to pick out certain data. One column has file names & the other has dates this file was submitted. The formulas I'm using a =SUMPRODUCT(--($C$2:$C$15403<"ABCD*"),(--(YEAR($I$2:$I$15403)=2007))) where "ABCD*" is the prefix for the file name. This seems to be ok, except I'm gett ALL of the files that were filed in 2007, not just the ones with the ABCD prefix. Any help is appreciated... Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct by year??
You can't directly use wildcards in SUMPRODUCT.
This seems to be ok, except I'm gett ALL of the files that were filed in 2007, not just the ones with the ABCD prefix. Your formula is counting all instances that *do not* start with the ABCD prefix. Try this: =SUMPRODUCT(--(LEFT($C$2:$C$15403,4)<"ABCD"),--(YEAR($I$2:$I$15403)=2007)) -- Biff Microsoft Excel MVP "MDI Anne" wrote in message ... I have a multi-columned worksheet, and am trying to pick out certain data. One column has file names & the other has dates this file was submitted. The formulas I'm using a =SUMPRODUCT(--($C$2:$C$15403<"ABCD*"),(--(YEAR($I$2:$I$15403)=2007))) where "ABCD*" is the prefix for the file name. This seems to be ok, except I'm gett ALL of the files that were filed in 2007, not just the ones with the ABCD prefix. Any help is appreciated... Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct by year??
Ok...now I want to do the same thing except the prefix changes to "WXYK"
Why can't I copy the formula & substitute?? it gives me the #VALUE! error... "Joel" wrote: I think you need LEFT and don't you want = not < =SUMPRODUCT(--(left($C$2:$C$15403,4)="ABCD"),(--(YEAR($I$2:$I$15403)=2007))) "MDI Anne" wrote: I have a multi-columned worksheet, and am trying to pick out certain data. One column has file names & the other has dates this file was submitted. The formulas I'm using a =SUMPRODUCT(--($C$2:$C$15403<"ABCD*"),(--(YEAR($I$2:$I$15403)=2007))) where "ABCD*" is the prefix for the file name. This seems to be ok, except I'm gett ALL of the files that were filed in 2007, not just the ones with the ABCD prefix. Any help is appreciated... Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct by year??
The only thing in your equation that would give a #Value error is if
I2:I15403 isn't a date. You would of seen this with the original formula for ABCD. There must be some bad data in the I column giving you the problem. I can't answer why you didn't see the problem with your original formula. "MDI Anne" wrote: Ok...now I want to do the same thing except the prefix changes to "WXYK" Why can't I copy the formula & substitute?? it gives me the #VALUE! error... "Joel" wrote: I think you need LEFT and don't you want = not < =SUMPRODUCT(--(left($C$2:$C$15403,4)="ABCD"),(--(YEAR($I$2:$I$15403)=2007))) "MDI Anne" wrote: I have a multi-columned worksheet, and am trying to pick out certain data. One column has file names & the other has dates this file was submitted. The formulas I'm using a =SUMPRODUCT(--($C$2:$C$15403<"ABCD*"),(--(YEAR($I$2:$I$15403)=2007))) where "ABCD*" is the prefix for the file name. This seems to be ok, except I'm gett ALL of the files that were filed in 2007, not just the ones with the ABCD prefix. Any help is appreciated... Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct by criteria, month, & year | Excel Worksheet Functions | |||
| Using COUNTIF or SUMPRODUCT for Year | Excel Discussion (Misc queries) | |||
| SUMPRODUCT - Exclude LAST Row of Matched Criteria (Month & Year) | Excel Worksheet Functions | |||
| sumproduct for month and year | Excel Discussion (Misc queries) | |||
| sumproduct in a given year | Excel Worksheet Functions | |||
Linear Mode
