sumproduct .. just not working

  • Thread starter Thread starter Chuck
  • Start date Start date
C

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
 
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
Click to expand...

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)
 
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
 
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
 
Back
Top