#1   Report Post  
Old December 5th 06, 04:08 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 48
Default Countif ?

How do I count the number of 2's in column A that do not have the word no in
column B? Answer = 3

A B
1 yes
2 no
2 yes
2 yes
1 maybe
2 maybe


  #2   Report Post  
Old December 5th 06, 04:33 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 1,726
Default Countif ?

=SUMPRODUCT(--(A1:A29=2),--(B2:B20="No"))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Tomkat743" wrote in message
...
How do I count the number of 2's in column A that do not have the word no
in
column B? Answer = 3

A B
1 yes
2 no
2 yes
2 yes
1 maybe
2 maybe



  #3   Report Post  
Old December 5th 06, 04:40 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,173
Default Countif ?

Tomkat

Something like

=SUMPRODUCT((A1:A6=2)*(B1:B6="No"))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Tomkat743" wrote in message
...
How do I count the number of 2's in column A that do not have the word no
in
column B? Answer = 3

A B
1 yes
2 no
2 yes
2 yes
1 maybe
2 maybe


  #4   Report Post  
Old December 5th 06, 05:19 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 48
Default Countif ?

This seems to be returning the quantity of no in column B where the value in
column A is 2. I need it to return the quantity of "not no" in column B. My
problem may be that I am trying to count actual text instead of numbers so I
have given an example of my actual formula. I only want to count the number
of "B" in column E if "GO BACK" does not appear in column O. Thanks for the
help!

=SUMPRODUCT(--('DISPATCH LOG 12.02.06'!$E:$E="B"),--('DISPATCH LOG
12.02.06'!$O:$O="GO BACK"))

"Bob Phillips" wrote:

=SUMPRODUCT(--(A1:A29=2),--(B2:B20="No"))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Tomkat743" wrote in message
...
How do I count the number of 2's in column A that do not have the word no
in
column B? Answer = 3

A B
1 yes
2 no
2 yes
2 yes
1 maybe
2 maybe




  #5   Report Post  
Old December 5th 06, 05:33 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 1,726
Default Countif ?

Simple adjustment

=SUMPRODUCT(--(A1:A29=2),--(B2:B20<"No"))


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Tomkat743" wrote in message
...
This seems to be returning the quantity of no in column B where the value
in
column A is 2. I need it to return the quantity of "not no" in column B.
My
problem may be that I am trying to count actual text instead of numbers so
I
have given an example of my actual formula. I only want to count the
number
of "B" in column E if "GO BACK" does not appear in column O. Thanks for
the
help!

=SUMPRODUCT(--('DISPATCH LOG 12.02.06'!$E:$E="B"),--('DISPATCH LOG
12.02.06'!$O:$O="GO BACK"))

"Bob Phillips" wrote:

=SUMPRODUCT(--(A1:A29=2),--(B2:B20="No"))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Tomkat743" wrote in message
...
How do I count the number of 2's in column A that do not have the word
no
in
column B? Answer = 3

A B
1 yes
2 no
2 yes
2 yes
1 maybe
2 maybe








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
sumproduct vs. countif Coal Miner Excel Discussion (Misc queries) 1 June 15th 06 09:34 PM
Countif Formula /Sort Bug??? orcfodder Excel Discussion (Misc queries) 2 January 12th 06 10:04 AM
COUNTIF or not to COUNTIF on a range in another sheet Ellie Excel Worksheet Functions 4 September 15th 05 10:06 PM
Combining IF and COUNTIF based on two columns maxtrixx Excel Discussion (Misc queries) 5 March 31st 05 06:21 PM
Countif - Countif maswinney Excel Worksheet Functions 3 November 15th 04 11:06 PM


All times are GMT +1. The time now is 09:39 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017