Google-sheets – Count occurrences of names so that only one in each row counts

formulasgoogle sheets

I have a set of data in one Sheet tab, and a counter on another tab in the same document.

I want the counter (in sheet 2) to read through the text in the table in sheet 1, and update how many times one name has occurred, counting only one per line. How would I do this?

I want it to end looking like in sample spreadsheet. Same example here: input data

name1   name2   name3   name4
name3   name5   name2   name2
name1   name2   name3   name3
name4   name1   name4   name2
name5   name3   name5   name1
name2   name4   name1   name5

Output (desired result) is:

name1   name2   name3   name4   name5
5       5       4       3       3

Best Answer

The following formula counts the rows of Data sheet which contain the string given in cell A1 of current sheet:

=countunique(arrayformula(if(Data!$C2:$F = A1, row(Data!$C2:$F), )))

Explanation: the arrayformula replaces each matching string with the row number, and leaves other cells blank. Then countunique counts the number of distinct entries within this array, which is exactly the number of rows.

Code

function onOpen() {
  var ss = SpreadsheetApp.getActive();
  var items = [{name: 'Magic', functionName: 'myMagic'}];
  ss.addMenu('Do Stuff', items);
}

function myMagic() {
  var ss = SpreadsheetApp.getActive(), aRow = ss.getActiveCell().getRow();
  var nDate = Utilities.formatDate(new Date(), "GMT-1", "dd-MM-yyyy");
  ss.getRange("B"+aRow).setValue(nDate);
}

Your magic needs to happen in the myMagic function.

Example

I've prepared an example file for you: button vs. menu
You can run the code and see it in the file itself.

Google-sheets – Alternate to nested if statements (alternate posing of question)

Ryan, I feel obliged to say that this isn't an ideal data setup. That said, if you're committed to keeping it, you can accomplish your goal with a version of the following (which I have placed in your second vertical block reading "January 2018":

=INDEX(14:14,MATCH(1,(ISNUMBER(15:15)*(15:15>0)),0)-2)&" "&INDEX(14:14,MATCH(1,(ISNUMBER(15:15)*(15:15>0)),0))

To use it elsewhere, just change the row range 14:14 to match your upper text row with the months and years, and change 15:15 to match the row where your currency amounts are.

HOW IT WORKS

First, spot the &" "& in the middle. Everything before this results in the month name. Everything after it results in the year. The ampersand(&) means "stick these together." So we will get monthname+onespace+year in the end.

The portion of the formula that retrieves the month and the portion that retrieves the year work similarly:

INDEX(14:14,_____)

In English, "Look across row 14."

MATCH(1,_____,0)

We are going to create a virtual row of true or false values that match up with every cell in row 14. In programming, a 1 means TRUE (or "Yes" or "I found it") and a 0 means FALSE (or "No" or "I didn't find it").

The zero at the end of the match means we want an exact match. It answers the question "Will you accept something close?" to which our answer is FALSE / NO (or, as I explained, 0 for that part).

ISNUMBER(15:15)*(15:15>0)

We are trying to test two conditions here in order to find our MATCH:

First condition: ISNUMBER(15:15) This tests every cell in row 15 to see if it is a number. Those cells that contain a number will earn a 1 (for TRUE/YES). Those cells that do not contain a number will get a 0 (FALSE/NO).

Second condition: (15:15>0) This is a second test on every cell in row 15 to see if it is contains a value greater than zero. If it does, that cell gets a 1; if not, it gets a 0.

The asterisk multiplies our first answer by our second answer. So for instance, if the first cell in row 15 held text, it would get a score of 0 (FALSE) for "Is it a number?" and 0 (FALSE) for "Is it greater than zero?" The cumulative score, then for that cell would be 0 * 0 or ... just 0.

As the formula looks across row 15, if it finds a negative number, the test returns 1 (TRUE) for "Is it a number?" and 0 (FALSE) for "Is it greater than zero?" The cumulative score for this cell will still be 0, because 1 * 0 = 0.

So the only cells that will get a 1 will be those that both contain a number and are greater than zero (which is what you're looking for).

In the end, let's say that row 15 had 25 cells in it. The virtual "array" (i.e., list) that ISNUMBER(15:15)*(15:15>0) creates will look something like this:

{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,1,0,0,0,0}

Remember, the MATCH function will be looking for the first instance of 1:

MATCH(1,(ISNUMBER(15:15)*(15:15>0)),0)

When it finds it, we know which column the first match is in.

Back to INDEX:

INDEX(14:14,MATCH(1,(ISNUMBER(15:15)*(15:15>0)),0))

INDEX will return the value in row 14 that matches the spot where MATCH found TRUE*TRUE (or 1). So we would now wind up with the YEAR from row 14 that's above the first numeric value greater than 0 in row 15.

But we want that second (which is why you'll see this exact formula portion after the &" "&).

In order to get the month, we just back up two cells from the year. So the first part of the formula, before &" "&, adds a -2:

INDEX(14:14,MATCH(1,(ISNUMBER(15:15)*(15:15>0)),0)-2)

In other words, look across row 14 until you find the TRUE/TRUE match in row 15, then back up 2 from where you find it.

In the end, we get:

=INDEX(14:14,MATCH(1,(ISNUMBER(15:15)*(15:15>0)),0)-2) (month name)

&" "& (and a single space and)

INDEX(14:14,MATCH(1,(ISNUMBER(15:15)*(15:15>0)),0)) (year)

Best Answer

Related Solutions

Google-sheets – Button in column C in each row

Code

Example

Google-sheets – Alternate to nested if statements (alternate posing of question)

Related Topic