Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif Column A and excule column B if it's cancelled
I have a spreadsheet that has column A with project type and column B with
the status. I need to count all projects of a certain type in column A that do not have a status = Cancelled in column B. The formulas as in another worksheet. I've been trying to use this formula but it's not correct: =COUNTIF(Projects!$A:$A,"Home")-COUNTIF(Projects!$B:$B,"Cancelled") I'm not sure if Countif is the right function to use. would Sumproduct be better? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif Column A and excule column B if it's cancelled
Hi!
Try this: =SUMPRODUCT(--(Projects!$A1:$A100="Home"),--(Projects!$B1:$B100<"Cancelled")) Note: you can't use entire columns as range arguments with Sumproduct (unless you're using Excel 2007 beta) Better to use cells to hold the criteria: C1 = Home C2 = Cancelled =SUMPRODUCT(--(Projects!$A1:$A100=C1),--(Projects!$B1:$B100<C2)) Biff "LLWest" wrote in message ... I have a spreadsheet that has column A with project type and column B with the status. I need to count all projects of a certain type in column A that do not have a status = Cancelled in column B. The formulas as in another worksheet. I've been trying to use this formula but it's not correct: =COUNTIF(Projects!$A:$A,"Home")-COUNTIF(Projects!$B:$B,"Cancelled") I'm not sure if Countif is the right function to use. would Sumproduct be better? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif Column A and excule column B if it's cancelled
try = sumproduct(--(A1:A64000="Home"),--(B1:B64000<"Cancelled"))
a specific range must be specified and not an entire column "LLWest" wrote: I have a spreadsheet that has column A with project type and column B with the status. I need to count all projects of a certain type in column A that do not have a status = Cancelled in column B. The formulas as in another worksheet. I've been trying to use this formula but it's not correct: =COUNTIF(Projects!$A:$A,"Home")-COUNTIF(Projects!$B:$B,"Cancelled") I'm not sure if Countif is the right function to use. would Sumproduct be better? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif Column A and excule column B if it's cancelled
that formula works great - thanks.
I have a column C now that has the revenue amount. how would I do the formual to add up the revenue of only the projects for that project type that aren't cancelled? thanks again "bj" wrote: try = sumproduct(--(A1:A64000="Home"),--(B1:B64000<"Cancelled")) a specific range must be specified and not an entire column "LLWest" wrote: I have a spreadsheet that has column A with project type and column B with the status. I need to count all projects of a certain type in column A that do not have a status = Cancelled in column B. The formulas as in another worksheet. I've been trying to use this formula but it's not correct: =COUNTIF(Projects!$A:$A,"Home")-COUNTIF(Projects!$B:$B,"Cancelled") I'm not sure if Countif is the right function to use. would Sumproduct be better? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif Column A and excule column B if it's cancelled
=SUMPRODUCT(--(Projects!A1:A100="Home"),--(Projects!B1:B100<"Cancelled"),Projects!C1:C100)
Biff "LLWest" wrote in message ... that formula works great - thanks. I have a column C now that has the revenue amount. how would I do the formual to add up the revenue of only the projects for that project type that aren't cancelled? thanks again "bj" wrote: try = sumproduct(--(A1:A64000="Home"),--(B1:B64000<"Cancelled")) a specific range must be specified and not an entire column "LLWest" wrote: I have a spreadsheet that has column A with project type and column B with the status. I need to count all projects of a certain type in column A that do not have a status = Cancelled in column B. The formulas as in another worksheet. I've been trying to use this formula but it's not correct: =COUNTIF(Projects!$A:$A,"Home")-COUNTIF(Projects!$B:$B,"Cancelled") I'm not sure if Countif is the right function to use. would Sumproduct be better? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|