Home / Questions / 398478
Accepted
Rafael Monroy Rodriguez
Asked: 2021-10-16 10:35:05 +0800 CST 2021-10-16 10:35:05 +0800 CST

Formula applied for several rows

  • 772

I am making a formula excelto subtract some cells depending on a logical value

=SI(Y(G11=DATOS!D2; B11="N2");C6-C11;0)

With this formula the value that I put in quantity is subtracted from the canvas number giving me the result, but this formula only works for that row, I am seeing how I can accommodate the formula so that it works for the rest of the rows and not only in row 11, I mean that if I put other data in row 12 and put a new amount, that amount is also subtracted with the canvas number and gives me the result in cell C7.

enter image description here

In the formula I am being very specific with the fields, for example saying B11="N2"but I also need that B12="N2"and so on with the cells down so that this formula works for the rest of the rows.

excel

2 Answers

  1. Best Answer

    So that it takes an initial number and subtracts the sum of a column, as long as 2 other columns match conditions:

    • C6- Initial number (Num Canvas)
    • C7- place where the formula goes, to show the result
    • C11:C99- Column to subtract from the initial number
    • B11:B99- Condition 1, this column has to beN2
    • G11:G99- Condition 2, this column must be equal to DATOS!$D$2( SUBCONTRATACIONin this example)

    Formula in C7 :

    =C$6 - SUMAR.SI.CONJUNTO($C$11:$C$99;$B$11:$B$99;"N2";$G$11:$G$99;DATOS!$D$2)

    It will add all the values ​​of the first parameter $C$11:$C$99, as long as the conditions of the rest match. In the formula SUMAR.SI.CONJUNTO()the first parameter is the range to add, and then come range, condition, range, condition,...etc.

    1. It will add cell 1 of $C$11:$C$99, only if it is true that cell 1 of $B$11:$B$99is equal to "N2", and only if cell 1 of $G$11:$G$99is equal toDATOS!$D$2
    2. It will add cell 2 of $C$11:$C$99, only if it is true that cell 2 of $B$11:$B$99is equal to "N2", and only if cell 2 of $G$11:$G$99is equal toDATOS!$D$2
    3. ...and so on for all cells in the range.

    For more information, read about the SUMIF.SET function .

    Example screenshot

    Screenshot with the result

    • Here it is taking 8 - 4 - 1 = 3( 4and 1they are the only ones that coincide with both conditions).
    • 2

  2. after having seen your query, and that you tell me that if it works in the first one and then the rest fails, and also knowing that C6it does not vary, I think your failure would be in that part.

    So that it does not vary C6you have to anchor it, you do this once selected with the key F4, or in any case placing the dollar symbol $before the column and row.

    This should help you.

    =SI(Y(G11=DATOS!D2; B11="N2");$C$6-C11;0)
    • 1