Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default counting non blank cells according to a condition

Hi there, I have a question that has been driving me nuts so if anyone can
assist, it would be much appreciated.
My spreadsheet looks like

A B
C
1 Weymouth 6/5/09 5/9/06
2 Dorchester 12/1/09 3/2/04

What I want to be able to do, is to say, "if A1 equals weymouth, and B1 is
not blank, count it".

Any ideas? Solving this will make a currently long winded manual counting
process take seconds for me at work. Any help appreciated!!


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default counting non blank cells according to a condition

Try this:

=SUMPRODUCT((A1="Weymouth")*(B1<""))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"hayleyho" wrote in message
...
Hi there, I have a question that has been driving me nuts so if anyone can
assist, it would be much appreciated.
My spreadsheet looks like

A B
C
1 Weymouth 6/5/09

5/9/06
2 Dorchester 12/1/09

3/2/04

What I want to be able to do, is to say, "if A1 equals weymouth, and B1 is
not blank, count it".

Any ideas? Solving this will make a currently long winded manual counting
process take seconds for me at work. Any help appreciated!!



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default counting non blank cells according to a condition

You could, of course, also use this function to count an entire range:

=SUMPRODUCT((A1:A100="Weymouth")*(B1:B100<""))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
Try this:

=SUMPRODUCT((A1="Weymouth")*(B1<""))
--
HTH,

RD

--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------

-

"hayleyho" wrote in message
...
Hi there, I have a question that has been driving me nuts so if anyone

can
assist, it would be much appreciated.
My spreadsheet looks like

A B
C
1 Weymouth 6/5/09

5/9/06
2 Dorchester 12/1/09

3/2/04

What I want to be able to do, is to say, "if A1 equals weymouth, and B1

is
not blank, count it".

Any ideas? Solving this will make a currently long winded manual

counting
process take seconds for me at work. Any help appreciated!!




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,510
Default counting non blank cells according to a condition

Expanding on the answer you can count the entire range like this.

=SUMPRODUCT((A1:A12="Weymouth")*(B1:B12<""))


--
Regards,

OssieMac


"Ragdyer" wrote:

Try this:

=SUMPRODUCT((A1="Weymouth")*(B1<""))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"hayleyho" wrote in message
...
Hi there, I have a question that has been driving me nuts so if anyone can
assist, it would be much appreciated.
My spreadsheet looks like

A B
C
1 Weymouth 6/5/09

5/9/06
2 Dorchester 12/1/09

3/2/04

What I want to be able to do, is to say, "if A1 equals weymouth, and B1 is
not blank, count it".

Any ideas? Solving this will make a currently long winded manual counting
process take seconds for me at work. Any help appreciated!!




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default counting non blank cells according to a condition

Hi to both that replied, I was indeed after the formula that includes the
range. I have just opened my spreadsheet to give it a go - it works a treat.
Many many thanks to both for your assistance, you have saved me so much
manual counting, I knew there had to be a way with excel!!
Kindest regards.

"Ragdyer" wrote:

You could, of course, also use this function to count an entire range:

=SUMPRODUCT((A1:A100="Weymouth")*(B1:B100<""))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
Try this:

=SUMPRODUCT((A1="Weymouth")*(B1<""))
--
HTH,

RD

--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------

-

"hayleyho" wrote in message
...
Hi there, I have a question that has been driving me nuts so if anyone

can
assist, it would be much appreciated.
My spreadsheet looks like

A B
C
1 Weymouth 6/5/09

5/9/06
2 Dorchester 12/1/09

3/2/04

What I want to be able to do, is to say, "if A1 equals weymouth, and B1

is
not blank, count it".

Any ideas? Solving this will make a currently long winded manual

counting
process take seconds for me at work. Any help appreciated!!







  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default counting non blank cells according to a condition

You're welcome, and we appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"hayleyho" wrote in message
...
Hi to both that replied, I was indeed after the formula that includes the
range. I have just opened my spreadsheet to give it a go - it works a

treat.
Many many thanks to both for your assistance, you have saved me so much
manual counting, I knew there had to be a way with excel!!
Kindest regards.

"Ragdyer" wrote:

You could, of course, also use this function to count an entire range:

=SUMPRODUCT((A1:A100="Weymouth")*(B1:B100<""))

--
HTH,

RD


--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit

!

--------------------------------------------------------------------------

-
"Ragdyer" wrote in message
...
Try this:

=SUMPRODUCT((A1="Weymouth")*(B1<""))
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may

benefit !

--------------------------------------------------------------------------
-

"hayleyho" wrote in message
...
Hi there, I have a question that has been driving me nuts so if

anyone
can
assist, it would be much appreciated.
My spreadsheet looks like

A B
C
1 Weymouth 6/5/09
5/9/06
2 Dorchester 12/1/09
3/2/04

What I want to be able to do, is to say, "if A1 equals weymouth, and

B1
is
not blank, count it".

Any ideas? Solving this will make a currently long winded manual

counting
process take seconds for me at work. Any help appreciated!!






  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,346
Default counting non blank cells according to a condition

Hi,

In 2007 you could use

=COUNTIFS(A1:A100,"Weymouth",B1:B100,"<")

instead of

=SUMPRODUCT((A1:100="Weymouth")*(B1:B100<""))

And if you entered Weymouth in F1 then both formulas would be more dynamic.

=COUNTIFS(A1:A100,F1,B1:B100,"<")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"hayleyho" wrote:

Hi there, I have a question that has been driving me nuts so if anyone can
assist, it would be much appreciated.
My spreadsheet looks like

A B
C
1 Weymouth 6/5/09 5/9/06
2 Dorchester 12/1/09 3/2/04

What I want to be able to do, is to say, "if A1 equals weymouth, and B1 is
not blank, count it".

Any ideas? Solving this will make a currently long winded manual counting
process take seconds for me at work. Any help appreciated!!


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
Counting Non Blank Cells Lindsey Excel Discussion (Misc queries) 4 April 8th 09 06:41 PM
Counting Non Blank Cells AJ[_2_] New Users to Excel 6 March 28th 08 12:06 AM
Counting non-blank cells Joe M. Excel Discussion (Misc queries) 7 July 23rd 07 09:36 PM
Counting blank cells geoff1234 Excel Discussion (Misc queries) 7 July 10th 06 11:29 AM
Counting the total number of cells with specified condition(freque Kelvin Excel Discussion (Misc queries) 2 July 10th 05 12:22 PM


All times are GMT +1. The time now is 06:31 PM.

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"