Is it possible to do a scan like function for excel?

Question

This is kinda complicated but lets say I have row A row B row C row D and row E. A,B are static numbers ,C,D have numbers in them that change a lot. E has a text identifier of two letters for example OP or MG. I want a formula that sums up A*(sum C)+(sumD) +B * (sumC)+ (sumD) for all the OP in column E. Kinda confusing but if i want to take all the OP from the E column and do calculations with them ?

Answer 1

you want to use the sumif() formula

assuming your A,B static numbers are really cell A1 and B1

the rest of the formula scans the entire column.

this is exactly the formula that you are asking for:

=A1*SUMIF(E:E,"OP",C:C)+ SUMIF(E:E,"OP",D:D)+ B1*SUMIF(E:E,"OP",C:C)+ SUMIF(E:E,"OP",D:D)

you should just be able to cut&paste

- you can change the "OP" to "MG" or some cell reference.  the cell reference would be better, that way you could make a table of unique identifiers you can refer to.  it also makes copying the formula easier.

- if A,B is supposed to sum the full column, then just replace A1 and B1 with sum(a:a) and sum(b:b)

- that (sum D) can just be multiplied by 2 since its not affected by numbers in A or B

good luck