标题需要根据条件添加2列的通用公式？ [英] Heading need general formula for addition of 2 columns based on conditions?

问题描述

我有3列。列A，列B，列C.列A包含条件，列B包含一些值，列C包含一些值。

Col A Col B Col C

  y 2 
y 3 
d 4 
y 5 2 
y 6 3 
n 7 
b 8 
y 9 
  

Sum =

我需要一个公式，当C列中没有值时，总和B的值，并且总和为Col C的所有值在列B但
中没有值当列B和列C都有值时，它更喜欢列C并将其值放在总和中，并且我只想添加列A中具有状态y的值。

示例：像列B的值为5，列C的值为2，因此我的公式应该优先选择2，并在总和中添加2 d留下5表示B列的值。
否则是简单的方法如果我有一个值在A只是添加这些值总和和如果我有值在B只是添加该值在总和。

我有一个公式，但它只适用于从两列添加值。
= SUMPRODUCT（ - （$ C2：C9 =y）; $ B2：B9 + $ C2：C9）这是从所有我有列，但我需要一个条件，当列B和C都有值，我需要更喜欢列C的值，只选择总和的C值。

喜欢/选择的公式1基于条件的2列的列值（For Addition）

我更简单的方法

只需从Col B和Col C添加y的所有值，但是当我们在Col B和Col C中都有值时，列C，并且只考虑列C的总和的值，忽略Col B的值

解决方案

我会有这样的逻辑：

如果A中为y，则如果C为空，则为B，否则为C。

  {= SUM（IF（$ A $ 2：$ A $ 10 = Y，IF（$ C $ 2：$ C $ 10 = ，$ B $ 2：$ B $ 10，$ C $ 2：$ C $ 10））}} 
  

这是一个数组公式。将其输入到单元格中，不带大括号，然后按[Ctrl] + [Shift] + [Enter]确认。大括号将自动出现。

I have 3 Columns. Column A, Column B, Column C. Column A contains conditions and Column B contains some value and Column C contains some values.
Col A Col B Col C

  y            2    
  y            3  
  d            4  
  y            5             2   
  y            6             3     
  n                          7  
  b                          8  
  y                          9     

Sum=

I need a Formula that sums values of B when there is no value in Column C and sum all values of Col C when there is no value in Column B but When there is value in both Column B and Column C then it prefer Column C and put its value in total sum and I want to add only values which have status y in Column A.

Example : like Column B has value = 5 and Column C has Value = 2 so my Formula should prefer 2 and add 2 in total sum and leave 5 means value of Column B . Else is simple means If I have Value in A just add those values in total Sum and If I have Value in B just add that Value in Total Sum.

I have a formula but it only works for adding values from both Columns. =SUMPRODUCT(--($C2:C9="y"); $B2:B9+$C2:C9) this is formula which adds value from all Columns that I have but I need a condition when Column B and C both have values and I need to prefer the value of Column C and choose only C value for total Sum.

Formula to prefer /choose 1 Column value from 2 columns based on conditions (For Addition)

I more simple way

Just Add all values for y from Col B and Col C but when We have values in both Col B and Col C then Prefer Column C and just consider value of Column C for total sum and ignore value of Col B

解决方案

I would have the logic like this:

Sum if "y" in A, then if C is empty, then B, else C.

{=SUM(IF($A$2:$A$10="y",IF($C$2:$C$10="",$B$2:$B$10,$C$2:$C$10)))}

This is an array formula. Input it into the cell without the curly brackets and press [Ctrl]+[Shift]+[Enter] to confirm. The curly brackets will then appear automatically.

这篇关于标题需要根据条件添加2列的通用公式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！