SumProduct & Countif?

[RayportingMonkey]

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="Q1",1,IF(Scorecard!$M$6="Q2",4,IF(Scorecard!$M$6="Q3",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

 

[Peo Sjoblom]

It's because you tell it to sum this part

LOB_ALT.xls!CV_FOR_VAR)


Remove that part and if it works as you say it will count the cells with the
criteria



--


Regards,


Peo Sjoblom

 

[T. Valko]

I need to COUNT the cells with a value between 0 and 10.

In what range? This one: LOB_ALT.xls!CV_FOR_VAR ?

If so, are there any empty cells or negative numbers in that range?

You can replace your nested IF's with:

LOOKUP(scorecard!M6,{"Q1","Q2","Q3","Q4"},{1,4,7,10})

 

[Teethless mama]

=SUBSTITUTE(scorecard!M6,"Q","")*3-2

 

[T. Valko]

=RIGHT(scorecard!M6)*3-2

 

[RayportingMonkey]

Hey Biff,

Thanks for the replies! First off, the options for cutting down my nested IF
statements were helpful! I'll add those to my bag of tricks!

As for the cells I need to COUNT with a value between 0 and 10, YES. They
are in the range named CV_FOR_VAR. And NO, there should not be any blanks.
There will be zeros as I implied, but other than that they will all be
positive intergers. The field is a variance of Actual vs. Forecasted numbers.

This is the last hurdle I have to get past to finish my project... I didn't
think it would be this complicated, but I guess I was wrong! I don't post
until I have exhausted my own resources and looked through other posts here
and elsewhere... Thanks for your help!

Later-
Ray

 

[T. Valko]

Try this:

=SUMPRODUCT(--(YEAR(LOB_ALT.xls!DateRange)=YEAR(TODAY()-1)),--(MONTH(LOB_ALT.xls!DateRange)=LOOKUP(scorecard!M6,{"Q1","Q2","Q3","Q4"},{1,4,7,10})),--(LOB_ALT.xls!CV_FOR_VAR<=10))

You can further reduce those nested IF's to:

=SUMPRODUCT(--(YEAR(LOB_ALT.xls!DateRange)=YEAR(TODAY()-1)),--(MONTH(LOB_ALT.xls!DateRange)=RIGHT(scorecard!M6)*3-2),--(LOB_ALT.xls!CV_FOR_VAR<=10))

However, the LOOKUP expression is slightly more efficient.

 

[RayportingMonkey]

That did it!

Thank you very much.

Try this:

=SUMPRODUCT(--(YEAR(LOB_ALT.xls!DateRange)=YEAR(TODAY()-1)),--(MONTH(LOB_ALT.xls!DateRange)=LOOKUP(scorecard!M6,{"Q1","Q2","Q3","Q4"},{1,4,7,10})),--(LOB_ALT.xls!CV_FOR_VAR<=10))

You can further reduce those nested IF's to:

=SUMPRODUCT(--(YEAR(LOB_ALT.xls!DateRange)=YEAR(TODAY()-1)),--(MONTH(LOB_ALT.xls!DateRange)=RIGHT(scorecard!M6)*3-2),--(LOB_ALT.xls!CV_FOR_VAR<=10))

However, the LOOKUP expression is slightly more efficient.

 

[T. Valko]

You're welcome. Thanks for the feedback!