Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Sum of 2 columns if one is filled?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Sum of 2 columns if one is filled?
=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" 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Sum of 2 columns if one is filled?
maybe...
=SUMPRODUCT(--(M2:M165="")*(E2:E165)) "TKGerdie" 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Sum of 2 columns if one is filled?
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" wrote: =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" 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Sum of 2 columns if one is filled?
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" 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? "Jim Cone" wrote: =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" 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filled cells dont appear as filled | Excel Discussion (Misc queries) | |||
Columns with missing data that needs filled in | Excel Worksheet Functions | |||
Select filled region only in non-adjacent columns | Excel Discussion (Misc queries) | |||
Last filled row | Excel Discussion (Misc queries) | |||
show me a filled in timecard example? | Setting up and Configuration of Excel |