sumproduct .. just not working

[Chuck]

hi guys,

im trying to calculate the value of all items that contain the same
year & same month of the product purchase along with the purchase code

i have a tab (POSummary) that contains all purchases which list the
date purchased & date received .. for now, i just want to pay
attention to date received .. as this is when i would have to
purchase .. .. eg, range POSummary!$I$3:$I$171

in the sheet i am trying to show the results, i have a row that shows
the month & year .. from say .. Jul 07 to June 08 and a clumn of
purchase codes for validation

the formula i am using is as follows

=SUMPRODUCT(--(MONTH(POSummary!$I$3:$I$171)=C$8),--(YEAR(POSummary!$I
$3:$I$171)=C$8),--(POSummary!$C$3:$C$171=$B59),(POSummary!$G$3:$G
$171))

so from the above, i have the following

where the data sits
POSummary!$I$3:$I$171 = date of receipt of purchase
POSummary!$G$3:$G$171 = total cost of each purchase
POSummary!$C$3:$C$171 = purchase code

where the figures will be displayed with validation criteria
C$8:N$8 = month & year for validation which displays Jul 1, 07 to June
1, 08
$B59:$B82 = purchase codes

if i use the formula above, i get no values what so ever.. however,
if i remove the YEAR portion of the formula, i will get results. the
catch is that some dates maybe blank within POSummary. i have found
through investigation that if the dates were blank, those items that
have been ordered, but not received (hence a blank date) will end up
being calculated in Month 01 (January)..

i need the formula to NOT actually calculate anything that has not
been received (date receive field blank / empty). for items that have
a blank value within POSummary I3:I171, there value is 1900-01-00
(this is an excel thing from what i can tell)

can someone advise what i am doing wrong here?

cheers
Chuck

 

[T. Valko]

where the figures will be displayed with validation criteria

C$8:N$8 = month & year for validation which displays Jul 1, 07 to June
1, 08

Are these true Excel dates?

Try replacing: =C$8

With: =MONTH(C$8) and =YEAR(C$8) in their respective arrays.

As long as you're testing for both month and year you shouldn't need to be
concerned with empty cells. (unless C8 is ever an empty cell)

 

[Peo Sjoblom]

If you have a full date in C8 then you need to wrap a date function on that
cell as well
Excel will NEVER accept a month and a year entered in a cell as a valid date
that you can make numerical comparisons with so it will convert it to one,
so try

--(MONTH(POSummary!$I$3:$I$171)=MONTH(C$8))

and

--(YEAR(POSummary!$I$3:$I$171)=YEAR(C$8))

if that does not work reformat the values in C8:N8 to mm/dd/yy format (or
whatever regional date setting you are using)

That way you can see if the year or month does not match

If I want to display something like Jul 07 I would type in

07/01/07 (once again US date format)

then custom format as

mmm yy


If the above does not work then you must have text in C8:N8


--


Regards,


Peo Sjoblom

 

[Chuck]

Valko & Peo

your simple solution worked great (i think both of you suggested the
same thing)

cheers for that.. dont know how such a simple solution was overlooked