| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
"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  100)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) |
|
|
|
|
|||
|
Guest
Posts: n/a
|
"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 100)> > > > 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) |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 100)> > > > > > 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 | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| count if equals first non error cell | John | Microsoft Excel Worksheet Functions | 6 | 12th Jan 2010 05:51 PM |
| Multiple Criteria & Count if Equals 0 | slf | Microsoft Excel Worksheet Functions | 4 | 3rd Oct 2009 07:24 AM |
| Set field value only if record count equals 1 | =?Utf-8?B?Tmlja3lH?= | Microsoft Access Form Coding | 1 | 29th Jun 2007 07:40 PM |
| Do not display record if grouping count equals X | david.isaacks@mail.va.gov | Microsoft Access Reports | 5 | 17th Jul 2006 03:45 PM |
| highlight cells equals sum, not count | =?Utf-8?B?UFRGaXNoZXI=?= | Microsoft Excel Misc | 2 | 4th Jun 2005 07:12 PM |
