Count the repeated number using VBA

[mvel_sky]

Dear Friends,

Please find the enclosed file for the problem.

Please help me to write a VBA code for this issue.

Thanks
M.Velmurugan

[sweep]

Hi,

Try this..

Please Login or Register  to view this content.

[Simon Lloyd]

Hmmm no VBA needed simply put this in in E3 and copy down =SUMPRODUCT((I3:I15=B3)*(J3:J15)) and this in D3 and copy down =SUMPRODUCT((I3:I15=B3)*(J3:J15=0))

[mvel_sky]

Thank You Very much... It is working...

But when i copy the formulae to my master file and modified accordingly, it is not working... All values are showing as zero... (File enclosed)

Please help...

[ravishankar]

Hi
If you had provided excel data instead of image, it would have been better.
try =sumproduct(($V$7:$V$59=E67)*($AF$7:$AF$59=0))
Ravi

[Simon Lloyd]

Thank You Very much... It is working...

But when i copy the formulae to my master file and modified accordingly, it is not working... All values are showing as zero... (File enclosed)

Please help...

The reason is you changed one of the ranges in the SUMPRODUCT to be inconsistent, SUMPRODUCT needs to have identical ranges so if you used A1:A100 in the criteria portion then you must use the same range dimensions in the rest i.e B1:B100 you cannot use something like B2:B59.

[mvel_sky]

Thanks for your valuable comments.

But if i enter the formulae in my original file, it is not working.

I have enclosed the original file for your ref...

Please do the needful.

Regards
M.Velmurugan

[Simon Lloyd]

Simply change the V in the formula to Q, you cannot sumproduct a concatenation!

[ravishankar]

Hi
see the attached file
Ravi