CODEWITHSUNDEEP
google-sheets
Alex McCraw
Alex McCraw

Reputation: 67

Using arrayformula with countif only up to a certain row

I have a sheet with a column of data where the entry is one of two strings, for simplicity's sake we'll say "A" and "B". I want another column of calculated data which is the differential between the number of "A"s and the number of "B"s up to that point, so just a countif()-countif() with the range increasing by one row. I can do this using =countif(A$2:A2, "A") - countif(A$2:A2, "B"), but it means I have to keep filling that equation down to cover any newly entered data, so I figured an array formula would be the best option to not have to do that. However when I try to use =arrayformula(countif(A$2:A, "A") - countif(A$2:A, "B")), it only populates a single cell with the difference counting the entire column. Is there a way to use an array formula so that it increases the range by one row for every row it populates?

Upvotes: 0

Views: 966

Answers (3)

Jerry Chong
Jerry Chong

Reputation: 9220

Alternatively, you may this IF statement:

=ARRAYFORMULA(IF(K2:K<>"",K2:K*B294,""))

Results (Above formula is applied on L2 row onwards):

enter image description here

Source: https://blog.sheetgo.com/google-sheets-formulas/arrayformula-google-sheets/

Upvotes: 0

iansedano
iansedano

Reputation: 6481

=ARRAYFORMULA("A2:A"&ROW(A2:A))

This will give a dynamic string for the right range, which in theory, you should be able to wrap with INDIRECT, to plug into COUNTIF.

But COUNTIF only works in some circumstances with ARRAY FORMULA

Unfortunately, AFAIK, only if you use COUNTIF like this:

=ARRAYFORMULA(COUNTIF(A1:A100,A1:A100))

will it work as an array formula. Note how both the ranges in the arguments are the same.

MMULT

There may be a way along these lines with MMULT:

=ARRAYFORMULA(
    MMULT(
        (A2:A100 = TRANSPOSE(A2:A100)) * (ROW(A2:A100) >= TRANSPOSE(ROW(A2:A100))),
        SIGN(ROW(A2:A100))
    )
)

But I couldn't get that to work because I am not 100% sure on how to use MMULT in this way, but I have seen that type of solution elsewhere.

An Apps Script Workaround

As you have seen, what you want to achieve with sheet functions quickly gets very complicated. If you wanted to use more letters or different rules, it would get tough to maintain. Apps Script can make these things much simpler.

So here is a custom Apps Script function:

function myFunction() {
  // Initializing
  let file = SpreadsheetApp.getActive();
  let sheet = file.getSheetByName("Sheet1");
  var lastRow = sheet.getLastRow();

  // This is the range of the As and Bs
  let range = sheet.getRange(2,1, parseInt(lastRow) - 1,1)
  let rows = range.getValues();

  // Creating an object to keep track of the count
  let tracker = {}

  // This will be the output column
  let newCol = []

  rows.forEach(row => {
    tracker[row[0]] += 1
    // Adding a row to the output
    newCol.push([tracker.A - tracker.B]) 
  })

  return newCol
}

Paste that into the script editor and you can use it like this:

Demo:

enter image description here

You can change the name of the function in the script editor according to what you need it for, just remember to call it with that name from the sheet.

References

Upvotes: 1

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(
 COUNTIFS(A2:A, A2:A, A2:A, "A", ROW(A2:A), "<="&ROW(A2:A))- 
 COUNTIFS(A2:A, A2:A, A2:A, "B", ROW(A2:A), "<="&ROW(A2:A)))

Upvotes: 0

Related Questions