=RIGHT(scorecard!M6)*3-2


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
=SUBSTITUTE(scorecard!M6,"Q","")*3-2


"RayportingMonkey" wrote:

I'm not sure of the best way to write this formula...

What I need to do is look to an external workbook for cells with a
specific
value that happen to be within a specified Month/Year combination

AND

Divide that number by a count of the total number of cells within the
same
Month/Year combination.

I have devised a SumProduct as follows:
=SUMPRODUCT(--(YEAR(LOB_ALT.xls!DATERANGE)=YEAR(TODAY()-1)),--(MONTH(LOB_ALT.xls!DATERANGE)=IF(Scorecard!$M$6="Q 1",1,IF(Scorecard!$M$6="Q2",4,IF(Scorecard!$M$6="Q 3",7,10)))),LOB_ALT.xls!CV_FOR_VAR)

But, all this does is SUM the cells I want... I need to COUNT the cells
with
a value between 0 and 10.

Is there a COUNTPRODUCT or something else I should be using other then
SUMPRODUCT???

Thanks,
Ray