| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct & Countif?
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct & Countif?
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 "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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct & Countif?
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct & Countif?
=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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct & Countif?
=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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct & Countif?
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct & Countif?
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct & Countif?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "RayportingMonkey" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif or Sumproduct | Excel Worksheet Functions | |||
| Sumproduct and Countif together | Excel Discussion (Misc queries) | |||
| CountIf or sumproduct | Excel Discussion (Misc queries) | |||
| Sumproduct or countif? | Excel Discussion (Misc queries) | |||
| SumProduct or CountIf | Excel Worksheet Functions | |||
Linear Mode
