Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If equals x AND y
Excel 2003
Col A Col B Col C 1 No Count this cell I need to count all the cells in Col C if Col A=1 and Col B=No Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If equals x AND y
I need to count all the cells in Col C if
Col A=1 and Col B=No Ok, what's the criteria for cells in column C? -- Biff Microsoft Excel MVP "Nadine" wrote in message ... Excel 2003 Col A Col B Col C 1 No Count this cell I need to count all the cells in Col C if Col A=1 and Col B=No Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If equals x AND y
"Nadine" wrote:
Excel 2003 Col A Col B Col C 1 No Count this cell I need to count all the cells in Col C if Col A=1 and Col B=No To "count" all the rows that meet that conditions in columns A and B: =sumproduct((A1:A100=1)*(B1:B100="no")) To __sum__ all the cells in column C that meet the conditions in columns A and B: =sumproduct((A1:A100=1)*(B1:B100="no"),C1:C100) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If equals x AND y
If you mean to count all of the non-blank cells in col C:
=SUMPRODUCT(($A$1:$A$100=1)*($B$1:$B$100="No")*($C $1:$C$100<"")) "Nadine" wrote in message ... Excel 2003 Col A Col B Col C 1 No Count this cell I need to count all the cells in Col C if Col A=1 and Col B=No Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If equals x AND y
Col C has the formula. Joe User answered my question. Thank you.
"T. Valko" wrote: I need to count all the cells in Col C if Col A=1 and Col B=No Ok, what's the criteria for cells in column C? -- Biff Microsoft Excel MVP "Nadine" wrote in message ... Excel 2003 Col A Col B Col C 1 No Count this cell I need to count all the cells in Col C if Col A=1 and Col B=No Thanks. . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If equals x AND y
Thanks so much Joe. I used the one to "count" and it worked perfectly. Now
I need to sum a different column based on the same condition used for the "count". for those that meet the same criteria, I now need to sum col D. Any ideas on that one? Thank you!!! "Joe User" wrote: "Nadine" wrote: Excel 2003 Col A Col B Col C 1 No Count this cell I need to count all the cells in Col C if Col A=1 and Col B=No To "count" all the rows that meet that conditions in columns A and B: =sumproduct((A1:A100=1)*(B1:B100="no")) To __sum__ all the cells in column C that meet the conditions in columns A and B: =sumproduct((A1:A100=1)*(B1:B100="no"),C1:C100) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If equals x AND y
"Nadine" wrote:
Now I need to sum a different column based on the same condition used for the "count". for those that meet the same criteria, I now need to sum col D. Isn't that simply a modification to the "sum" formula that I already provided, to wit: =sumproduct((A1:A100=1)*(B1:B100="no"),D1:D100) If that does not work for you, you will need to be more clear about your requirements. ----- original message ----- "Nadine" wrote: Thanks so much Joe. I used the one to "count" and it worked perfectly. Now I need to sum a different column based on the same condition used for the "count". for those that meet the same criteria, I now need to sum col D. Any ideas on that one? Thank you!!! "Joe User" wrote: "Nadine" wrote: Excel 2003 Col A Col B Col C 1 No Count this cell I need to count all the cells in Col C if Col A=1 and Col B=No To "count" all the rows that meet that conditions in columns A and B: =sumproduct((A1:A100=1)*(B1:B100="no")) To __sum__ all the cells in column C that meet the conditions in columns A and B: =sumproduct((A1:A100=1)*(B1:B100="no"),C1:C100) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If equals x AND y
Here's what I want:
Sum all the amounts in column E if Col A=1 AND Col B=No. The formula is being written in cell F2. I don't know how to write a SUMIF with 2 conditions that both need to be there. I know how to do it with the IF formula but not SUMIF. Thanks. "Joe User" wrote: "Nadine" wrote: Now I need to sum a different column based on the same condition used for the "count". for those that meet the same criteria, I now need to sum col D. Isn't that simply a modification to the "sum" formula that I already provided, to wit: =sumproduct((A1:A100=1)*(B1:B100="no"),D1:D100) If that does not work for you, you will need to be more clear about your requirements. ----- original message ----- "Nadine" wrote: Thanks so much Joe. I used the one to "count" and it worked perfectly. Now I need to sum a different column based on the same condition used for the "count". for those that meet the same criteria, I now need to sum col D. Any ideas on that one? Thank you!!! "Joe User" wrote: "Nadine" wrote: Excel 2003 Col A Col B Col C 1 No Count this cell I need to count all the cells in Col C if Col A=1 and Col B=No To "count" all the rows that meet that conditions in columns A and B: =sumproduct((A1:A100=1)*(B1:B100="no")) To __sum__ all the cells in column C that meet the conditions in columns A and B: =sumproduct((A1:A100=1)*(B1:B100="no"),C1:C100) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If equals x AND y
"Nadine" wrote:
Sum all the amounts in column E if Col A=1 AND Col B=No. First you say you want to sum column D; now you say column E. But really, what difference does it make? Try: =sumproduct((A1:A100=1)*(B1:B100="no"),E1:E100) If the issue is: you want to write A:A, B:B and E:E instead of explicit ranges like A1:A100, B1:B100 and E1:E100, well, you cannot in Excel 2003. (I believe someone has said you can in Excel 2007.) If would still like to avoid explicit ranges (e.g. A1:A100), please articulate that fact. If that is not the issue, and if the above SUMPRODUCT does not work for you, please explain why not. A concrete example might help. ----- original message ----- "Nadine" wrote: Here's what I want: Sum all the amounts in column E if Col A=1 AND Col B=No. The formula is being written in cell F2. I don't know how to write a SUMIF with 2 conditions that both need to be there. I know how to do it with the IF formula but not SUMIF. Thanks. "Joe User" wrote: "Nadine" wrote: Now I need to sum a different column based on the same condition used for the "count". for those that meet the same criteria, I now need to sum col D. Isn't that simply a modification to the "sum" formula that I already provided, to wit: =sumproduct((A1:A100=1)*(B1:B100="no"),D1:D100) If that does not work for you, you will need to be more clear about your requirements. ----- original message ----- "Nadine" wrote: Thanks so much Joe. I used the one to "count" and it worked perfectly. Now I need to sum a different column based on the same condition used for the "count". for those that meet the same criteria, I now need to sum col D. Any ideas on that one? Thank you!!! "Joe User" wrote: "Nadine" wrote: Excel 2003 Col A Col B Col C 1 No Count this cell I need to count all the cells in Col C if Col A=1 and Col B=No To "count" all the rows that meet that conditions in columns A and B: =sumproduct((A1:A100=1)*(B1:B100="no")) To __sum__ all the cells in column C that meet the conditions in columns A and B: =sumproduct((A1:A100=1)*(B1:B100="no"),C1:C100) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If equals x AND y
As for it now being Col E...it's because I used Col D for something else
while I was waiting for a response. Unfortunately the result of your formula below is #VALUE. :( Here's some of my data: No No $- 1 Yes $7,011.08 1 Yes $1,017.77 No No $- 1 Yes $23,205.00 1 Yes $68,300.82 1 Yes $24,477.37 1 Yes $7,023.52 1 Yes $739.25 1 Yes $16,977.94 No No $- 1 Yes $14,056.64 1 Yes $6,949.76 1 No $8,890.43 No No $17,287.55 1 No $776.36 1 Yes $18,512.61 1 No $21,168.08 1 Yes $5,335.93 1 No $28,880.00 1 No $54,493.77 1 Yes $5,362.08 1 No $41,173.60 1 No $- 1 Yes $18,390.56 No No $14,952.88 1 Yes $14,886.16 1 Yes $37,225.00 1 No $8,676.44 1 Yes $10,824.89 "Joe User" wrote: "Nadine" wrote: Sum all the amounts in column E if Col A=1 AND Col B=No. First you say you want to sum column D; now you say column E. But really, what difference does it make? Try: =sumproduct((A1:A100=1)*(B1:B100="no"),E1:E100) If the issue is: you want to write A:A, B:B and E:E instead of explicit ranges like A1:A100, B1:B100 and E1:E100, well, you cannot in Excel 2003. (I believe someone has said you can in Excel 2007.) If would still like to avoid explicit ranges (e.g. A1:A100), please articulate that fact. If that is not the issue, and if the above SUMPRODUCT does not work for you, please explain why not. A concrete example might help. ----- original message ----- "Nadine" wrote: Here's what I want: Sum all the amounts in column E if Col A=1 AND Col B=No. The formula is being written in cell F2. I don't know how to write a SUMIF with 2 conditions that both need to be there. I know how to do it with the IF formula but not SUMIF. Thanks. "Joe User" wrote: "Nadine" wrote: Now I need to sum a different column based on the same condition used for the "count". for those that meet the same criteria, I now need to sum col D. Isn't that simply a modification to the "sum" formula that I already provided, to wit: =sumproduct((A1:A100=1)*(B1:B100="no"),D1:D100) If that does not work for you, you will need to be more clear about your requirements. ----- original message ----- "Nadine" wrote: Thanks so much Joe. I used the one to "count" and it worked perfectly. Now I need to sum a different column based on the same condition used for the "count". for those that meet the same criteria, I now need to sum col D. Any ideas on that one? Thank you!!! "Joe User" wrote: "Nadine" wrote: Excel 2003 Col A Col B Col C 1 No Count this cell I need to count all the cells in Col C if Col A=1 and Col B=No To "count" all the rows that meet that conditions in columns A and B: =sumproduct((A1:A100=1)*(B1:B100="no")) To __sum__ all the cells in column C that meet the conditions in columns A and B: =sumproduct((A1:A100=1)*(B1:B100="no"),C1:C100) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count if equals first non error cell | Excel Worksheet Functions | |||
Multiple Criteria & Count if Equals 0 | Excel Worksheet Functions | |||
If a cell equals _, at the next row that equals _, return value fr | Excel Worksheet Functions | |||
highlight cells equals sum, not count | Excel Discussion (Misc queries) | |||
custom filter does not work when selecting 'equals' X AND 'equals' | Excel Discussion (Misc queries) |