Feb 10 2022 02:58 PM
How to implement the following
SUM(IF(LEFT(A201:A210)="CAPITAL",C201:C210,0))
which will add values in C201 through C210 when A201 through A210 has the value "CAPITAL"
Feb 10 2022 03:06 PM
Feb 10 2022 03:49 PM
*
@ is the intersection operator
To be more precise:
SPACE is the intersection operator.
@ is the reference operator aka implicit intersection operator.
Feb 10 2022 05:12 PM
Feb 10 2022 05:16 PM
It is in the support article for the function:
If num_chars is omitted, it is assumed to be 1.
Feb 10 2022 05:20 PM - edited Feb 10 2022 05:21 PM
Yes, I know it.
But how can a 1-length string be equal to "CAPITAL"?
Feb 10 2022 05:25 PM - edited Feb 10 2022 05:32 PM
SolutionIt should be Left(A201:A210, 7). If omitted, it defaults to one - I just copied the existing formula and did not catch that. But, to be honest, I'm not sure I entirely understand the question. It does not appear the existing formula should spill, with the sum function.
Perhaps this is closer to what you are trying do?
=IF(LEFT(@A201:A210, 7)="CAPITAL",@C201:C210,0)
Or, perhaps enter the existing formula with Ctrl+Shift+Enter, or replace sum with sumproduct?
SUMPRODUCT(--(LEFT(A201:A210, 7)="CAPITAL"),C201:C210)
Feb 10 2022 05:27 PM
Feb 10 2022 05:41 PM - edited Feb 10 2022 06:17 PM
@radinhj IF i am not wrong you want the cell in C to sum up corresponding to column A contains CAPITAL?
if it is directly use sumif OR SUMPRODUCT function
=SUMPRODUCT((A201:A210="CAPITAL")*(C201:C210))
=SUMIF(A201:A210,"CAPITAL",C201:C210)
Feb 11 2022 06:54 AM
Thanks. This only works if the value is CAPITAL but not if the value contains CAPITAL.
The question I posed is somewhat simplified since i am actually looking to include multiple complex conditions (using AND and OR within the IF statement)
Feb 11 2022 07:28 AM
Feb 11 2022 08:33 AM
Feb 10 2022 05:25 PM - edited Feb 10 2022 05:32 PM
SolutionIt should be Left(A201:A210, 7). If omitted, it defaults to one - I just copied the existing formula and did not catch that. But, to be honest, I'm not sure I entirely understand the question. It does not appear the existing formula should spill, with the sum function.
Perhaps this is closer to what you are trying do?
=IF(LEFT(@A201:A210, 7)="CAPITAL",@C201:C210,0)
Or, perhaps enter the existing formula with Ctrl+Shift+Enter, or replace sum with sumproduct?
SUMPRODUCT(--(LEFT(A201:A210, 7)="CAPITAL"),C201:C210)