Array? IF? LOOKUP? VBA?

[noneofyourbiznez]

What is the most efficient way to separately compute the sum of cells
from rows 1:10, across [an array] of multiple columns, if I want to
assign a numerical value of 100 to all cells that have the value "x" in
the cell?

* I am not allowed to alter the formatting of the worksheet, ergo the
"x" values must remain in the cells;

* I do not want to create extra (ghost) columns just to change the "x"
values to 100.


Essentially, I want to perform this function:

=SUM(A1:A10) (and SUM(B1:B10), etc)

while imposing the condition that =IF((A$1:A$10)="x",100,A$1:A$10)



Thanks in advance for suggestions!

 

[Guest]

use -
100*countif(A1:A100,x)+sumif(A1:A100,<>x)

 

[noneofyourbiznez]

Thanks for the assistance... still having trouble. When I apply your
formula to this table, I only get the sum of COUNTIF argument:

red red 0
10 red 0
1 red 0
red red 0
10 red 0
1 10 0
red 10 0
10 10 0
1 10 1
0 10 10

=((100*COUNTIF(A1:A10,"red"))+(SUMIF(A1:A10,A1<>"red")))

300 500 0

The answer I want to get is:

333 550 11


Thanks again.

use -
100*countif(A1:A100,x)+sumif(A1:A100,<>x)

What is the most efficient way to separately compute the sum of cells
from rows 1:10, across [an array] of multiple columns, if I want to
assign a numerical value of 100 to all cells that have the value "x" in
the cell?

* I am not allowed to alter the formatting of the worksheet, ergo the
"x" values must remain in the cells;

* I do not want to create extra (ghost) columns just to change the "x"
values to 100.


Essentially, I want to perform this function:

=SUM(A1:A10) (and SUM(B1:B10), etc)

while imposing the condition that =IF((A$1:A$10)="x",100,A$1:A$10)



Thanks in advance for suggestions!

 

[Bob Phillips]

Use

=SUM(A1:A20)+COUNTIF(A1:A20,"red")*100

and copy across.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thanks for the assistance... still having trouble. When I apply your
formula to this table, I only get the sum of COUNTIF argument:

red red 0
10 red 0
1 red 0
red red 0
10 red 0
1 10 0
red 10 0
10 10 0
1 10 1
0 10 10

=((100*COUNTIF(A1:A10,"red"))+(SUMIF(A1:A10,A1<>"red")))

300 500 0

The answer I want to get is:

333 550 11


Thanks again.

use -
100*countif(A1:A100,x)+sumif(A1:A100,<>x)

What is the most efficient way to separately compute the sum of cells
from rows 1:10, across [an array] of multiple columns, if I want to
assign a numerical value of 100 to all cells that have the value "x" in
the cell?

* I am not allowed to alter the formatting of the worksheet, ergo the
"x" values must remain in the cells;

* I do not want to create extra (ghost) columns just to change the "x"
values to 100.


Essentially, I want to perform this function:

=SUM(A1:A10) (and SUM(B1:B10), etc)

while imposing the condition that =IF((A$1:A$10)="x",100,A$1:A$10)



Thanks in advance for suggestions!

 

[Bob Phillips]

Sorry, should be

=SUM(A1:A10)+COUNTIF(A1:A10,"red")*100

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[noneofyourbiznez]

Duh, of course!

Thanks Bob!

Sorry, should be

=SUM(A1:A10)+COUNTIF(A1:A10,"red")*100

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

What is the most efficient way to separately compute the sum of cells
from rows 1:10, across [an array] of multiple columns, if I want to
assign a numerical value of 100 to all cells that have the value "x" in
the cell?

* I am not allowed to alter the formatting of the worksheet, ergo the
"x" values must remain in the cells;

* I do not want to create extra (ghost) columns just to change the "x"
values to 100.


Essentially, I want to perform this function:

=SUM(A1:A10) (and SUM(B1:B10), etc)

while imposing the condition that =IF((A$1:A$10)="x",100,A$1:A$10)



Thanks in advance for suggestions!