If two cells have specific text to count as 1
I want to count the number of times a row has two specific words, in two
different cells eg. If column A contains the word 'Widget' and column B contains the word 'Sale' I want to count the number of times this occurs. I'm sure this is easy but I've spent hours trying to get it to work. |
If two cells have specific text to count as 1
Check out Chip Pearson's Array Formula explanation...
http://www.cpearson.com/excel/array.htm An array formula is created using Ctrl+Shift+Enter to surround the formula with braces { }. You can't just type them :O. Your formula would look something like... {=SUM(IF($A1:$A1000="widget",1,0)*IF($B1:$B1000="s ale",1,0))} Make sure you read Chip's site (above) to understand 'WHY' this works. Otherwise I've given you a meal instead of helped you learn how to fish for yourself. HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Jazzman10" wrote: I want to count the number of times a row has two specific words, in two different cells eg. If column A contains the word 'Widget' and column B contains the word 'Sale' I want to count the number of times this occurs. I'm sure this is easy but I've spent hours trying to get it to work. |
If two cells have specific text to count as 1
Thanks for that - it works a treat and I read through Chip Pearson's array
formula explanation. However here's another question you might be able to solve: The formula works fine if put into a cell that hasn't been used before. But if I try to enter (ctrl+shift+enter) it into a cell that has previously had a deleted formula in it just puts the formula in without making it an array formula (no {} either side) and won't work? "Gary L Brown" wrote: Check out Chip Pearson's Array Formula explanation... http://www.cpearson.com/excel/array.htm An array formula is created using Ctrl+Shift+Enter to surround the formula with braces { }. You can't just type them :O. Your formula would look something like... {=SUM(IF($A1:$A1000="widget",1,0)*IF($B1:$B1000="s ale",1,0))} Make sure you read Chip's site (above) to understand 'WHY' this works. Otherwise I've given you a meal instead of helped you learn how to fish for yourself. HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Jazzman10" wrote: I want to count the number of times a row has two specific words, in two different cells eg. If column A contains the word 'Widget' and column B contains the word 'Sale' I want to count the number of times this occurs. I'm sure this is easy but I've spent hours trying to get it to work. |
If two cells have specific text to count as 1
You could try a normal sumproduct and avoid the hassle of having to worry about CTRL Shift and Enter =SUMPRODUCT((A1:A1000="widget")*(B1:B1000="sale")) Don't know about your missing {} in the array version. Wha happens if you copy and paste the array formula? Rgds On Mon, 26 Jun 2006 07:59:01 -0700, Jazzman10 wrote: Thanks for that - it works a treat and I read through Chip Pearson's array formula explanation. However here's another question you might be able to solve: The formula works fine if put into a cell that hasn't been used before. But if I try to enter (ctrl+shift+enter) it into a cell that has previously had a deleted formula in it just puts the formula in without making it an array formula (no {} either side) and won't work? "Gary L Brown" wrote: Check out Chip Pearson's Array Formula explanation... http://www.cpearson.com/excel/array.htm An array formula is created using Ctrl+Shift+Enter to surround the formula with braces { }. You can't just type them :O. Your formula would look something like... {=SUM(IF($A1:$A1000="widget",1,0)*IF($B1:$B1000="s ale",1,0))} Make sure you read Chip's site (above) to understand 'WHY' this works. Otherwise I've given you a meal instead of helped you learn how to fish for yourself. HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Jazzman10" wrote: I want to count the number of times a row has two specific words, in two different cells eg. If column A contains the word 'Widget' and column B contains the word 'Sale' I want to count the number of times this occurs. I'm sure this is easy but I've spent hours trying to get it to work. __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
If two cells have specific text to count as 1
I can't duplicate your issue. It works first time, every time for me.
-- Gary Brown "Jazzman10" wrote: Thanks for that - it works a treat and I read through Chip Pearson's array formula explanation. However here's another question you might be able to solve: The formula works fine if put into a cell that hasn't been used before. But if I try to enter (ctrl+shift+enter) it into a cell that has previously had a deleted formula in it just puts the formula in without making it an array formula (no {} either side) and won't work? "Gary L Brown" wrote: Check out Chip Pearson's Array Formula explanation... http://www.cpearson.com/excel/array.htm An array formula is created using Ctrl+Shift+Enter to surround the formula with braces { }. You can't just type them :O. Your formula would look something like... {=SUM(IF($A1:$A1000="widget",1,0)*IF($B1:$B1000="s ale",1,0))} Make sure you read Chip's site (above) to understand 'WHY' this works. Otherwise I've given you a meal instead of helped you learn how to fish for yourself. HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Jazzman10" wrote: I want to count the number of times a row has two specific words, in two different cells eg. If column A contains the word 'Widget' and column B contains the word 'Sale' I want to count the number of times this occurs. I'm sure this is easy but I've spent hours trying to get it to work. |
All times are GMT +1. The time now is 08:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com