|
Sumproduct using wildcard for month only
I am working on an excel spreadsheet to put together a report by using values
on our master worksheet. The following information is what I am trying to use: Column AI - contains date values in MM/DD/YY format. Column Z - contains a status (Complete, Installed etc are potential values) Column L - contains the numerical value of the units I want to sum. I want to know What the total number of units that are Installed in September 09 is.... here is what I am trying, but its not working! =SUMPRODUCT(--(MONTH('Transit and PINPAD Listing'!F4:F1047)=9), --('Transit and PINPAD Listing'!Z4:Z1047="Installed"), --('Transit and PINPAD Listing'!L4:L1047)) I need to have the blanks ignored..... HELP! |
Sumproduct using wildcard for month only
=SUMPRODUCT(--('Transit and PINPAD
Listing'!F4:F1047)=DATE(2009,9,1),--('Transit and PINPAD Listing'!F4:F1047)<=DATE(2009,9,30), --('Transit and PINPAD Listing'!Z4:Z1047="Installed"), --('Transit and PINPAD Listing'!L4:L1047)) "Princess V" wrote: I am working on an excel spreadsheet to put together a report by using values on our master worksheet. The following information is what I am trying to use: Column AI - contains date values in MM/DD/YY format. Column Z - contains a status (Complete, Installed etc are potential values) Column L - contains the numerical value of the units I want to sum. I want to know What the total number of units that are Installed in September 09 is.... here is what I am trying, but its not working! =SUMPRODUCT(--(MONTH('Transit and PINPAD Listing'!F4:F1047)=9), --('Transit and PINPAD Listing'!Z4:Z1047="Installed"), --('Transit and PINPAD Listing'!L4:L1047)) I need to have the blanks ignored..... HELP! |
Sumproduct using wildcard for month only
Thanks Sean....I see what you are doing here...
I even found an error on my part...my date is listed in Column AI, not F....so I changed my formula to the following: =SUMPRODUCT(--('Transit and PINPAD Listing'!AI4:AI1047)=DATE(2009,9,1),--('Transit and PINPAD Listing'!AI4:AI1047)<=DATE(2009,9,30),--('Transit and PINPAD Listing'!Z4:Z1047="Installed"),--('Transit and PINPAD Listing'!L4:L1047)) However, this yields a value of 0....I know that this is not the case.... What else can i check.... "Sean Timmons" wrote: =SUMPRODUCT(--('Transit and PINPAD Listing'!F4:F1047)=DATE(2009,9,1),--('Transit and PINPAD Listing'!F4:F1047)<=DATE(2009,9,30), --('Transit and PINPAD Listing'!Z4:Z1047="Installed"), --('Transit and PINPAD Listing'!L4:L1047)) "Princess V" wrote: I am working on an excel spreadsheet to put together a report by using values on our master worksheet. The following information is what I am trying to use: Column AI - contains date values in MM/DD/YY format. Column Z - contains a status (Complete, Installed etc are potential values) Column L - contains the numerical value of the units I want to sum. I want to know What the total number of units that are Installed in September 09 is.... here is what I am trying, but its not working! =SUMPRODUCT(--(MONTH('Transit and PINPAD Listing'!F4:F1047)=9), --('Transit and PINPAD Listing'!Z4:Z1047="Installed"), --('Transit and PINPAD Listing'!L4:L1047)) I need to have the blanks ignored..... HELP! |
Sumproduct using wildcard for month only
This worked!!! Excellent!!!!!!! Thank you so much! "T. Valko" wrote: What exactly isn't working? I need to have the blanks ignored..... The only time empty cells would be included is when you test for month 1 (Jan). You can either include another array that tests for the year: --(YEAR('Transit and PINPAD Listing'!F4:F1047)=2009) Or, you can test for both month and year at the same time like this: --(TEXT('Transit and PINPAD Listing'!F4:F1047,"m/yyyy")="1/2009") What do think about this idea? 'Transit and PINPAD Listing' = T&PList -- Biff Microsoft Excel MVP "Princess V" <Princess wrote in message ... I am working on an excel spreadsheet to put together a report by using values on our master worksheet. The following information is what I am trying to use: Column AI - contains date values in MM/DD/YY format. Column Z - contains a status (Complete, Installed etc are potential values) Column L - contains the numerical value of the units I want to sum. I want to know What the total number of units that are Installed in September 09 is.... here is what I am trying, but its not working! =SUMPRODUCT(--(MONTH('Transit and PINPAD Listing'!F4:F1047)=9), --('Transit and PINPAD Listing'!Z4:Z1047="Installed"), --('Transit and PINPAD Listing'!L4:L1047)) I need to have the blanks ignored..... HELP! |
Sumproduct using wildcard for month only
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Princess V" wrote in message ... This worked!!! Excellent!!!!!!! Thank you so much! "T. Valko" wrote: What exactly isn't working? I need to have the blanks ignored..... The only time empty cells would be included is when you test for month 1 (Jan). You can either include another array that tests for the year: --(YEAR('Transit and PINPAD Listing'!F4:F1047)=2009) Or, you can test for both month and year at the same time like this: --(TEXT('Transit and PINPAD Listing'!F4:F1047,"m/yyyy")="1/2009") What do think about this idea? 'Transit and PINPAD Listing' = T&PList -- Biff Microsoft Excel MVP "Princess V" <Princess wrote in message ... I am working on an excel spreadsheet to put together a report by using values on our master worksheet. The following information is what I am trying to use: Column AI - contains date values in MM/DD/YY format. Column Z - contains a status (Complete, Installed etc are potential values) Column L - contains the numerical value of the units I want to sum. I want to know What the total number of units that are Installed in September 09 is.... here is what I am trying, but its not working! =SUMPRODUCT(--(MONTH('Transit and PINPAD Listing'!F4:F1047)=9), --('Transit and PINPAD Listing'!Z4:Z1047="Installed"), --('Transit and PINPAD Listing'!L4:L1047)) I need to have the blanks ignored..... HELP! |
Sumproduct using wildcard for month only
Hi,
If all of your dates are in the same year you could use: =SUMPRODUCT(--(S=A1),--(MONTH(D)=9),V) By using range names instead of cell references you avoid the complexity of sheet references. In this example I entered the status (Installed) in cell A1. And range named the date D, the status S and the values V. If the dates can be in multiple years then =SUMPRODUCT(--(S=A23),--(TEXT(D,"m/yy")="9/09"),V) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Princess V" wrote: Thanks Sean....I see what you are doing here... I even found an error on my part...my date is listed in Column AI, not F....so I changed my formula to the following: =SUMPRODUCT(--('Transit and PINPAD Listing'!AI4:AI1047)=DATE(2009,9,1),--('Transit and PINPAD Listing'!AI4:AI1047)<=DATE(2009,9,30),--('Transit and PINPAD Listing'!Z4:Z1047="Installed"),--('Transit and PINPAD Listing'!L4:L1047)) However, this yields a value of 0....I know that this is not the case.... What else can i check.... "Sean Timmons" wrote: =SUMPRODUCT(--('Transit and PINPAD Listing'!F4:F1047)=DATE(2009,9,1),--('Transit and PINPAD Listing'!F4:F1047)<=DATE(2009,9,30), --('Transit and PINPAD Listing'!Z4:Z1047="Installed"), --('Transit and PINPAD Listing'!L4:L1047)) "Princess V" wrote: I am working on an excel spreadsheet to put together a report by using values on our master worksheet. The following information is what I am trying to use: Column AI - contains date values in MM/DD/YY format. Column Z - contains a status (Complete, Installed etc are potential values) Column L - contains the numerical value of the units I want to sum. I want to know What the total number of units that are Installed in September 09 is.... here is what I am trying, but its not working! =SUMPRODUCT(--(MONTH('Transit and PINPAD Listing'!F4:F1047)=9), --('Transit and PINPAD Listing'!Z4:Z1047="Installed"), --('Transit and PINPAD Listing'!L4:L1047)) I need to have the blanks ignored..... HELP! |
| All times are GMT +1. The time now is 04:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com