The formula does not work when adding ArrayFormula

I am making a 3D print log so I can track my prints, their material cost, and c

     IF(B:B="Black", (O2*D2:D)/1000, 
        IF(B:B="White", (O3*D2:D)/1000,
           IF(B:B="Blue", (O4*D2:D)/1000,
              IF(B:B="Yellow", (O5*D2:D)/1000,
                 IF(B:B="Special", (O6*D2:D)/1000,
                    IF(B:B="Mixed", (O7*D2:D)/1000,
                       IF(B:B="Wood", (O8*D2:D)/1000,
                          IF(B:B="Carbon", (O9*D2:D)/1000, 0
             )
          )
       )
    )
)))))

The command does not work when adding ArrayFormula

I am making a 3D print log so I can track my prints, their material cost, and c

     IF(B:B="Black", (O2*D2:D)/1000, 
        IF(B:B="White", (O3*D2:D)/1000,
           IF(B:B="Blue", (O4*D2:D)/1000,
              IF(B:B="Yellow", (O5*D2:D)/1000,
                 IF(B:B="Special", (O6*D2:D)/1000,
                    IF(B:B="Mixed", (O7*D2:D)/1000,
                       IF(B:B="Wood", (O8*D2:D)/1000,
                          IF(B:B="Carbon", (O9*D2:D)/1000, 0
             )
          )
       )
    )
)))))

google sheets – ID assignment automatically using Arrayformula and text

I am probably approaching this badly but I am trying to automatically assign an ID to an element, I would like the format to be C #### where #### starts with 0s and is assigned as 0001.

The expected result would be C0001, C0002, C0003 … C9999

={"UID"; ARRAYFORMULA(if(D2:D="", "", ("C"&(text(row(D2:D)-1), "####"))))}

Without the text format, you have no problem assigning the first element as C1. If I assign the previous 0, it would be generated as C0001 … C00011 instead of C0011.

Thanks in advance for your help.

ArrayFormula / SumIf for several columns according to the dynamic date / Monday to Friday

I have several form response sheets (which I import into a common workbook) that need to show a total from Monday to Friday of the week that is updated for the current week from Monday to Friday. I thought it could be an ArrayFormula (SumIf … based on the date range, but I can't understand it. Besides this … Then I need to check the same totals Monday through Friday on a summary sheet within this same book of I work … but I think I have that part solved for now. Any help to solve this would be greatly appreciated! 🙂

Do you use Google Apps Script with arrayformula?

I am using an array formula in Google Sheets to determine when a cell shows & # 39; Expired & # 39; according to the date.

When the cell shows Expired, I have a script that must transfer rows with Expired to another sheet.

The script works to transfer when manually entered Expired in the corresponding column, but does not recognize when the data array enters this.

Wondering if there is any solution for this?

Screenplay below:

onEdit function (event) {

var ss = SpreadsheetApp.getActiveSpreadsheet ();
var s = event.source.getActiveSheet ();
var r = event.source.getActiveRange ();

if (s.getName () == "Open" && r.getColumn () == 5 && r.getValue () == "Expired") {
var row = r.getRow ();
var numColumns = s.getLastColumn ();
var targetSheet = ss.getSheetByName ("Expired");
var target = targetSheet.getRange (targetSheet.getLastRow () + 1, 1);
s.getRange (row, 1, 1, numColumns) .moveTo (target);
s.deleteRow (row);
}
}
}

Google spreadsheets: incorrect and somewhat strange results when using COUNTIF with Arrayformula

I have three functions responsible for verifying if I own an action (seeing that column F is not empty). Then, I want to check column AB to see what market the stock comes from. In the first case, I want to calculate the amount of large capitalization companies in which I have shares.

However, my formulas seem to recalculate incorrectly. Sometimes they don't change at all when I add new actions to my list. The next three functions are the functions I am using. (Note that these are European separators, so ";" should not be changed, for example, to ",".

=COUNTIFs(('ISK Investeringar'!$F$3:$F$103); "<>" ; ArrayFormula(vlookup(('ISK Investeringar'!$F$3:$F$103);sort({'ISK Investeringar'!$F$3:$F$103'ISK Investeringar'!$AB$3:$AB$103};2;false);2;false)); "Large Cap" )/D3

=(COUNTIFs(('ISK Investeringar'!$F$3:$F$103); "<>" ; ArrayFormula(vlookup(('ISK Investeringar'!$F$3:$F$103);sort({'ISK Investeringar'!$F$3:$F$103'ISK Investeringar'!$AB$3:$AB$103};2;false);2;false)); "Mid Cap") + COUNTIFs(('ISK Investeringar'!$F$3:$F$103); "<>" ; ArrayFormula(vlookup(('ISK Investeringar'!$F$3:$F$103);sort({'ISK Investeringar'!$F$3:$F$103'ISK Investeringar'!$AB$3:$AB$103};2;false);2;false)); "Small Cap"))/D3

=COUNTIFs(('ISK Investeringar'!$F$3:$F$103); "<>" ; ArrayFormula(vlookup(('ISK Investeringar'!$F$3:$F$103);sort({'ISK Investeringar'!$F$3:$F$103'ISK Investeringar'!$AB$3:$AB$103};2;false);2;false)); "First North" )/D3

With these 3 formulas and, for example, with 7 different actions in the following markets:

Long coat
Long coat
Long coat
Long coat
ETF
Medium capitalization
First north

I get the following percentages:
71% big cap
14% average capitalization
0% first north

Can anyone see what is wrong in this formula for the purpose described above? I have the same type of formula to see in how many unique branches I have companies and that function works perfectly. I will share that function below:

=COUNTIFs( UNIQUE('ISK Investeringar'!$C$3:$C$103); "<>"; ArrayFormula(vlookup(unique('ISK Investeringar'!$C$3:$C$103);sort({'ISK Investeringar'!$C$3:$C$103'ISK Investeringar'!$F$3:$F$103};2;false);2;false)); "<>" )

So, I would like to know why my first 3 formulas do not work correctly, but the last one does.

Thanks in advance

Getting the error # DIV / 0 in Google Sheets for AVERAGE, SUM, COUNT, etc. in the column generated by ARRAYFORMULA

I am trying to get the average value of a column (column C "Profit Margin" in the screenshot) in Google Sheets. For some reason, I get a # DIV / 0 error when I try to use any type of range functions such as SUM, AVERAGE, COUNT, etc. in this column:

enter the description of the image here

I feel it is related to the fact that column C was generated using ARRAYFORMULA, but I'm not sure …

Here is a link to a copy of the spreadsheet if you want to see the data: BuyBack sample spreadsheet

formulas: Can I use arrayformula in Google Sheets to count things in a variable range?

Formula

=ArrayFormula(MMULT(TRANSPOSE((ROW(B1:B9)<=TRANSPOSE(ROW(B1:B9)))*ISTEXT(B1:B9)*1),ISTEXT(B1:B9)*1))

What you are looking to do is called "cumulative total."

The above formula uses matrices, TRANSPOSE, MMULT (matrix multiplication), ISTEXT and unit multiplication (to analyze TRUE as 1 and FALSE as 0) instead of creating a COUNTA formula chain

The first argument of MMULT create a variant of a lower triangular matrix (all values ​​above the main diagonal are zero). Instead of all the lower values ​​being 1, they are 1 if the corresponding values ​​are text values ​​and 0 if not.

NOTE: Not all spreadsheet functions can handle an array as an argument. INDIRECT It is one of those functions.

formulas: can I use arrayformula in Google Sheets to count things in a changing range?

Is it possible to create a single array of formulas that does the same as the following formula column:

A1: =COUNTA(B1:B1)
A2: =COUNTA(B1:B2)
A3: =COUNTA(B1:B3)
...
A9: =COUNTA(B1:B9)

My best attempt was =ARRAYFORMULA(COUNTA(INDIRECT("B$1:"&B1:9)) but I get a circular dependency error. I guess Arrayformula doesn't know which of the two possible ranges to expand. I would need to tell you which set to spend and which one not. Maybe there is a notation like "::" for that? It would be great anyway.

Google spreadsheets – Sumproduct within Arrayformula

This is my formula in the cell D2:

=SUMPRODUCT(B:B=B2;F:F>F2)+1

The values ​​in the column are all of the same length and are continually updated using contributions from Google forms. Therefore, a condition based on length makes no sense.

How can I make an Arrayformula of this, so that the B2& # 39; s and F2& # 39; s change with row numbers?