Prev: Regression Formula
Next: inventory
From: Nadine on 5 May 2010 16:11
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)
First  |  Prev  | 
Pages: 1 2 3
Prev: Regression Formula
Next: inventory