SumProduct & Countif?
That did it!
Thank you very much.
"T. Valko" wrote:
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.
--
Biff
Microsoft Excel MVP
"RayportingMonkey" wrote in
message ...
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" wrote:
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,1 0})
--
Biff
Microsoft Excel MVP
"RayportingMonkey" wrote in
message ...
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
|