Finding a number.................

[sssb2000]

hello everyone,
i have the following range: A1:B10
in A1:A10, i have a formula that populates the cells with numbers. Only one of these cells can/will have a number in it at any given time.

In B1:B10, i have a static number for each cell.

Now, in C1, i need a formula that says: if A1 is non-zero, then add the value of A1 to B1 and report it in C1 ..... OR....if A2 is non-zero, then add the value of A2 to B2 and report in C1....OR....(same all the way to A10).

how can i do this without an IF statement?

 

[T. Valko]

hello everyone,
i have the following range: A1:B10
in A1:A10, i have a formula that populates the cells with numbers. Only one of these cells can/will have a number in it at any given time.

In B1:B10, i have a static number for each cell.

Now, in C1, i need a formula that says: if A1 is non-zero, then add the value of A1 to B1 and report it in C1 ..... OR....if A2 is non-zero, then add the value of A2 to B2 and report in C1....OR....(same all the way to A10).

how can i do this without an IF statement?

One way...

Book1
	A	B	C
1	_	22	86
2	_	41	_
3	_	16	_
4	_	57	_
5	60	26	_
6	_	43	_
7	_	62	_
8	_	42	_
9	_	29	_
10	_	20	_
Sheet1

This formula entered in C1:

=MAX(A1:A10)+SUMIF(A1:A10,"<1E100",B1:B10)

 

[sssb2000]

that didn't work.....it just added everything in column B and put it in C1.
my column A has all zeros and only one cell that has a non-zero value.

 

[T. Valko]

that didn't work.....it just added everything in column B and put it in C1.
my column A has all zeros and only one cell that has a non-zero value.

Are there any negative numbers in A1:A10?

If not then try this:

=MAX(A1:A10)+SUMIF(A1:A10,">0",B1:B10)

 

[sssb2000]

Are there any negative numbers in A1:A10?

If not then try this:

=MAX(A1:A10)+SUMIF(A1:A10,">0",B1:B10)

perfect
many thanks!

 

[T. Valko]

perfect
many thanks!

You're welcome. Thanks for the feedback!