Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Non Blank Cells | Excel Discussion (Misc queries) | |||
Counting Non Blank Cells | New Users to Excel | |||
Counting non-blank cells | Excel Discussion (Misc queries) | |||
Counting blank cells | Excel Discussion (Misc queries) | |||
Counting the total number of cells with specified condition(freque | Excel Discussion (Misc queries) |