In E2 (replace the commas with semi-colons for Calc): This is where the function SEARCH (case insensitive, as opposed to the case-sensitive function FIND) came in. I had to do something more like “does the URL contain a certain domain”. Therefore, I couldn’t do a simple comparison. The actual spreadsheet I was working on involved full URLs containing the domain: E2 is essentially summing 1 * 2 and 1 * 4 for the rows and 0 * the count for all the other rows.
In E2 (replace comma with semi-colon for Calc): This involves testing the key (Domain) the result of our test would be TRUE, which has a value of 1, or FALSE, which has a value of 0.
However, we only want to include certain rows if they match our criteria. Rather, you would use the function SUMPRODUCT, which multiples two columns and sums, well, the products. The solution doesn’t actually involve VLOOKUP, although it is somewhat in the spirit of VLOOKUP. What if you wanted to sum the values of all of the “” counts? If you want to display the count for “” in a different cell, you would use the function VLOOKUP, whose basic use is nicely described here.īut as in the screenshot above, certain domains such as “” appear several times. Make sure you use "Regular Expressions" (hit More):Īfter that you'll find your numbers are really numbers and that there's no apostrophe ' in front of them.Supposing you have a spreadsheet with keys (Domains) and values (Count): Just do a find and replace but don't choose "Current selection only", because the cells in the selection don't show it so it will say "Search key not found". *Easy solution with regular expression searches within the whole Calc sheet There is supposedly some work around involving changing the default language or something, this bug has been around for years and the devs would rather flame the users than even acknowledge and fix it. I don't have time for this, I've read belligerent comments in the OpenOffice forums from what seems like a dev who refuses to admit this is a bug. You can't even do a standard search and replace since it's a weird bug that ony shows itself when you click an individual cell that is affected. This is typical OpenSource software that keeps introducing bugs into the simplest of operations and tasks. The only solution is search and replace ' to blank but it's ridiculous. If I change it to be currency it still does the same thing! What a stupid bug in Calc/Openoffice. I notice when I click the cell there is an invisible ' in front of the number:Ĭalc imported my CSV and decided to add a ' in front of the numeric currency values!!!*Actually the problem occurs when you right click the cells and change the type from text to number, no matter what format you choose. I right clicked all applicable cells and chose "number" but that's enough.
0 kommentar(er)
