sumproduct and percentage

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Got myself in muddle. I have the code below.

I want to search all those records in column F with less than 12 characters
but only for that month and year identified by cells H5 and J5 respectfully.
i get a result but it is way off.

=SUMPRODUCT(--(LEN(Sheet1!$F2:$F30000)<12),--(MONTH(Sheet1!$I$2:$I$30000)=H5),--(YEAR(Sheet1!$I$2:$I$30000)=J5))/(F12+G12+H12+I12+J12+K12)

cells F12,G12,H12,I12,J12,K12 just contain raw data.

Any ideas? I think I have got lost with the brackets.
 
In what way is the result way off?
what is being calculated is the number of cells meeting the criteria divided
by the sum of f12:k12.
A common error is that date text is really text and not a date.
a quick way to check this would be to select column I change the format of
the date and see if all of the cells changed.
another quick check is to put into a cell
=counta(I2:I3000)
and then then change it to
=count(I2:I3000)
if they are not the same number and you have no lables in the range, some
of the dates will be date text.
 
Back
Top