Array? IF? LOOKUP? VBA?
 

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!

Array? IF? LOOKUP? VBA?
 

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

" wrote:

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!

Array? IF? LOOKUP? VBA?
 

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<"r ed")))

300 500 0

The answer I want to get is:

333 550 11


Thanks again.



עודד wrote:
use -
100*countif(A1:A100,x)+sumif(A1:A100,<x)

" wrote:

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!

Array? IF? LOOKUP? VBA?
 

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)

wrote in message
oups.com...
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<"r ed")))

300 500 0

The answer I want to get is:

333 550 11


Thanks again.



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

" wrote:

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!

Array? IF? LOOKUP? VBA?
 

Sorry, should be

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

--
HTH

Bob Phillips

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

wrote in message
oups.com...
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!

Array? IF? LOOKUP? VBA?
 

Duh, of course!

Thanks Bob!



Bob Phillips wrote:
Sorry, should be

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

--
HTH

Bob Phillips

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

wrote in message
oups.com...
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!