LinkBack URL
About LinkBacks
Please see attached example. I need a formula for Cell F2 that sums the number of entries in columns A, B, and C, but is not duplicative. In other words, I only want one entry in each row to count toward the sum.
Please see attached example. I need a formula for Cell F2 that sums the number of entries in columns A, B, and C, but is not duplicative. In other words, I only want one entry in each row to count toward the sum.
Try this
=SUMPRODUCT((A2:C6<>"")/COUNTIF(A2:C6,A2:C6&""))
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Try this;
=COUNT(A2:B6)-SUMPRODUCT((A2:A6<>"")*(B2:B6<>""))
You should hit F5, because chances are I've edited this post at least 5 times.
Example of Array Formulas
Click the * below on any post that helped you.Originally Posted by Jacc
Please see attached spreadsheet, instead of the original one I uploaded. I need the formulas for Cells A8:A14. Also, note that the number of rows on the second sheet could be in the hundreds (or thousands), so ideally the formulas will account for this.
Sorry, please use this attachment.
Same formula? This will go to 50,000 rows.
=COUNT('Current Quarter'!B9:C50000)-SUMPRODUCT(('Current Quarter'!B9:B50000<>"")*('Current Quarter'!C9:C50000<>""))
Note: Merged Cells should be avoided like the plague at all times. They are horrible for writing formulas, macros, and formatting. The range B8:D8 can be unmerged, the the "A" placed in cell C8 then centered, it will look the same, but won't ruin your spreadsheet later. Just a tip!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks