Need Sum of 2 columns if one is filled?

[TKGerdie]

I need help with a formula. I have two columns - E (projected revenue) and M
(actual revenue). If M has a total in it, I want to ignore the same row in
E, so that the actual revenue is added in to the total sum at the bottom
along with the projected (if there isn't an actual amount) in column M. The
sum is E166. The columns are E2:E165 and M2:M165.

 

[Jim Cone]

=SUMPRODUCT(--(M2:M165<0.001),E2:E165)+SUM(M2:M165)
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"TKGerdie" <[email protected]>
wrote in message
I need help with a formula. I have two columns - E (projected revenue) and M
(actual revenue). If M has a total in it, I want to ignore the same row in
E, so that the actual revenue is added in to the total sum at the bottom
along with the projected (if there isn't an actual amount) in column M. The
sum is E166. The columns are E2:E165 and M2:M165.

 

[Gaurav]

maybe...

=SUMPRODUCT(--(M2:M165="")*(E2:E165))

 

[TKGerdie]

Thanks Jim - that seems to work. But can you explain the logic on the
formula . . . what is this --( telling the formula to do?

 

[Jim Cone]

You are welcome. As to why the --?

The m2:m165<0.001 part of the formula is not just returning a single value it
actually returns a true/false value for every cell in the range.
The first minus sign then converts each true or false to a number (-1 or 0) then
the second minus sign converts the -1 values to a positive value.
(a zero is always zero).
The formula can then use the converted true/false values to do the calculation.

You can examine a portion of a formula from the formula bar by highlighting
a logical portion of the formula and pressing the F9 key.
Do that with the "m2:m165<0.001" part of the formula and see what you get.
Press the Escape key to exit - don't use the enter key.

Bob Phillips has a very long article on the entire concept at his website...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Jim Cone
Portland, Oregon

..

"TKGerdie" <[email protected]>
wrote in message
Thanks Jim - that seems to work. But can you explain the logic on the
formula . . . what is this --( telling the formula to do?