Sumproduct using wildcard for month only

[Princess V]

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!

 

[Sean Timmons]

=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))

 

[T. Valko]

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

 

[Princess V]

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....

 

[Princess V]

This worked!!! Excellent!!!!!!!

Thank you so much!

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Shane Devenshire]

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)