sumproduct and percentage
 

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.

--
Regards vipa

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.

"vipa2000" wrote:

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.

--
Regards vipa