Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 216
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 905
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 216
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 216
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 905
Default 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"),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)

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 216
Default 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"),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)

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 905
Default 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"),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)

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 216
Default 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"),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)

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
count if equals first non error cell John Excel Worksheet Functions 6 January 12th 10 06:51 PM
Multiple Criteria & Count if Equals 0 slf Excel Worksheet Functions 4 October 3rd 09 07:24 AM
If a cell equals _, at the next row that equals _, return value fr CathyH Excel Worksheet Functions 10 May 2nd 07 07:53 PM
highlight cells equals sum, not count PTFisher Excel Discussion (Misc queries) 2 June 4th 05 07:12 PM
custom filter does not work when selecting 'equals' X AND 'equals' plindman Excel Discussion (Misc queries) 1 June 1st 05 11:29 PM


All times are GMT +1. The time now is 10:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"