Count If equals x AND y
in [Functions]
|
Prev: Regression Formula
Next: inventory
From: Nadine on 5 May 2010 13:15 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" <Nadine(a)discussions.microsoft.com> wrote in message > news:5374D99B-BCA4-485C-BDCF-E3F8C6D3D4B7(a)microsoft.com... > > 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. > > > . >
From: Nadine on 5 May 2010 13:20 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)
From: Joe User on 5 May 2010 13:40 "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)
From: Nadine on 5 May 2010 15:41 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)
From: Joe User on 5 May 2010 15:55
"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) |