|
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!! |
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!! |
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!! |
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!! |
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!! |
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!! |
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!! |
| All times are GMT +1. The time now is 02:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com