Count If equals x AND y

[Nadine]

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.

 

[T. Valko]

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?

 

[Ms-Exl-Learner]

Try this...

=SUMPRODUCT(($A1:$A65535=1)*($B1:$B65535="NO"),($C1:$C65535))

Remember to Click Yes, if this post helps!

 

[Joe User]

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)

 

[Steve Dunn]

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]

Col C has the formula. Joe User answered my question. Thank you.

 

[Nadine]

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]

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 -----

 

[Joe User]

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]

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