Counting rows WITHOUT double counting the values in individual columns.
All right, so I've got data consisting of approximately 1000 rows, with 3 columns. Really simple stuff, but I can't for the life of me figure out how to just purely count the rows that have at least 1 value in 1 column.
I have attached a screenshot and the xls file.
Counting 101.jpg
Thanks in advance! Cheers!
I would put this in D4, then copy down.
=IF(OR(ISNUMBER(SEARCH("Consider",F4&G4&H4)),ISNUMBER(SEARCH("Yes",F4&G4&H4))), 1, "")
Then just sum column D.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
You may try this in column D (start at row 4 and copy down):
Formula:
Please Login or Register to view this content.
Notice the brackets surrounding the formula meaning it's an array formula. Close with [Ctrl]+[Shift]+[Enter]
Cheers!
Tsjallie
--------
If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!
If you think design is an expensive waste of time, try doing without ...
(NO EXTRA COLUMNS!)
Hi all- this ARRAY FORMULA* does the calculation in a single cell :
=SUM(--(FREQUENCY(IF((F4:H17="Yes")+(F4:H17="Consider"),ROW(F4:H17)),ROW(F4:H17))>0))
*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.
Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
Last edited by leelnich; 07-13-2017 at 05:36 AM.
2 more ways.
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks