Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Could somebody explain to me why this formula is not counting my data.
SUMPRODUCT((Projected!I:I="Pilot")*(Projected!O:O= "FY2009-Q4")) I think I have written it correctly. I am trying to have the formula look at a worksheet and count if the cell in row I equals pilot and the cell in row O equals FY2009-Q4. For some reason excel is not able to calculate the formula. On a note, this formula used to calculate, but I have a macro select all the cells and delete before importing the data with column headers and formatting. After I deleted the cells and repasted the new data, it is not calculating even after I engage a recalc. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Hello,
Don't use I:I and O:O but something like I1:I100 and O1:O100 or buy Excel 2007. Regards, Bernd |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Unless you are using Excel 2007, you can't use whole columns with SUMPRODUCT.
This works for me (edit range to match your data): =SUMPRODUCT((Projected!I1:I10000="Pilot")*(Project ed!O1:O10000="FY2009-Q4")) Hope this helps, Hutch "dwake" wrote: Could somebody explain to me why this formula is not counting my data. SUMPRODUCT((Projected!I:I="Pilot")*(Projected!O:O= "FY2009-Q4")) I think I have written it correctly. I am trying to have the formula look at a worksheet and count if the cell in row I equals pilot and the cell in row O equals FY2009-Q4. For some reason excel is not able to calculate the formula. On a note, this formula used to calculate, but I have a macro select all the cells and delete before importing the data with column headers and formatting. After I deleted the cells and repasted the new data, it is not calculating even after I engage a recalc. Any help would be appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Hi,
The formula works fine in E2007 but in earlier versions you can't use full columns. What result are you getting? Mike "dwake" wrote: Could somebody explain to me why this formula is not counting my data. SUMPRODUCT((Projected!I:I="Pilot")*(Projected!O:O= "FY2009-Q4")) I think I have written it correctly. I am trying to have the formula look at a worksheet and count if the cell in row I equals pilot and the cell in row O equals FY2009-Q4. For some reason excel is not able to calculate the formula. On a note, this formula used to calculate, but I have a macro select all the cells and delete before importing the data with column headers and formatting. After I deleted the cells and repasted the new data, it is not calculating even after I engage a recalc. Any help would be appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
I'm getting a number error.
"Mike H" wrote: Hi, The formula works fine in E2007 but in earlier versions you can't use full columns. What result are you getting? Mike "dwake" wrote: Could somebody explain to me why this formula is not counting my data. SUMPRODUCT((Projected!I:I="Pilot")*(Projected!O:O= "FY2009-Q4")) I think I have written it correctly. I am trying to have the formula look at a worksheet and count if the cell in row I equals pilot and the cell in row O equals FY2009-Q4. For some reason excel is not able to calculate the formula. On a note, this formula used to calculate, but I have a macro select all the cells and delete before importing the data with column headers and formatting. After I deleted the cells and repasted the new data, it is not calculating even after I engage a recalc. Any help would be appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
I'm getting a number error.
Then if you read either my first ressponse or either of the other 2 you have then you'll see what the remedy is!! Mike "dwake" wrote: I'm getting a number error. "Mike H" wrote: Hi, The formula works fine in E2007 but in earlier versions you can't use full columns. What result are you getting? Mike "dwake" wrote: Could somebody explain to me why this formula is not counting my data. SUMPRODUCT((Projected!I:I="Pilot")*(Projected!O:O= "FY2009-Q4")) I think I have written it correctly. I am trying to have the formula look at a worksheet and count if the cell in row I equals pilot and the cell in row O equals FY2009-Q4. For some reason excel is not able to calculate the formula. On a note, this formula used to calculate, but I have a macro select all the cells and delete before importing the data with column headers and formatting. After I deleted the cells and repasted the new data, it is not calculating even after I engage a recalc. Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
SUMPRODUCT | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct | Excel Discussion (Misc queries) |