If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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"),D1100) 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
|
|||
|
|||
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"),D1100) 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
|
|||
|
|||
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"),D1100) 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
|
|||
|
|||
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"),D1100) 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) |
Thread Tools | |
Display Modes | |
|
|