Google-sheets – How to count cell with upper leter in array

formulasgoogle sheetsgoogle-calculatorgoogle-sheets-arrayformularegexextract

I need to count the upper letter if exist in a particular cell. So my results should be as on the image below. important is ignore in count empty cell.

I noticed that task is simple if I can use languages like JavaScript, or Java function. There, operation on a string is easy, but if I must use a function in google calc this task for me it becomes difficult.

EDIT

I Found solution but it is ugly, long, not readable, and I can't see on that, but works
(in PL language)

=LICZ.JEŻELI(ARRAYFORMULA(CZY.LICZBA(JEŻELI(CZY.PUSTA(F8:AL8);"";ZNAJDŹ(F8:AL8;"A")))*1) ; 1) + LICZ.JEŻELI(ARRAYFORMULA(CZY.LICZBA(JEŻELI(CZY.PUSTA(F8:AL8);"";ZNAJDŹ(F8:AL8;"B")))*1) ; 1) + LICZ.JEŻELI(ARRAYFORMULA(CZY.LICZBA(JEŻELI(CZY.PUSTA(F8:AL8);"";ZNAJDŹ(F8:AL8;"C")))*1) ; 1)

Best Answer

=ARRAYFORMULA(IF(LEN(A1:A&B1:B&C1:C&D1:D&E1:E), 
 MMULT(IFERROR(LEN(IFERROR(REGEXEXTRACT(A1:E, "[A-Z]+")))/
               LEN(IFERROR(REGEXEXTRACT(A1:E, "[A-Z]+"))), 0), 
 TRANSPOSE(COLUMN(A1:E1)^0)), ))

Related Solutions

Google-sheets – How to COUNT rows with ARRAYFORMULA

In addition to previous posts, I think I would go for a query function. Although not that short , it will eliminate the need 1) to drag the current formula down (newly added rows will 'automatically be added to the output of the query) and 2) to have the unique formula to populate col A on the count sheet.

=query(Incidents!B2:C; "select B, count(C) where B<>''  group by B label B 'Name', count(C) 'Number of incidents' ")

EDIT: Closer to the topic, if a list of names is already given (in col A), a count in arrayformula should be possible by doing:

=ArrayFormula(countif(split(concatenate(if(len(Incidents!B2:B),Incidents!B2:B&char(9),)),char(9),0), filter(A:A, len(A:A))))

Google Sheets – Array Formula with Running Total Restarting Based on Another Column

It's easier to write a custom function for this than to look for a MMULT solution that may not even exist. The function would be used as

=runningtotal(B2:B12, A2:A12, "New")

where B2:B12 is the range with data, A2:A12 is the range with conditions for restart, and "New" is the value that tells that the total should restart.

The script runs a loop over the data, aggregates the total in total, and restarts when needed.

function runningTotal(data, conditions, str) {
  var output = [];
  var total = 0;
  for (var i in data) {
    total = (conditions[i][0] == str ? data[i][0] : total + data[i][0]);
    output.push([total]);
  }
  return output;
}

Best Answer

Related Solutions

Google-sheets – How to COUNT rows with ARRAYFORMULA

Google Sheets – Array Formula with Running Total Restarting Based on Another Column

Related Topic