Forum Discussion
Solved
How to simulate old array formulas that yield one result instead of spilling
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"
It 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)
I apologize for the sloppiness if omitting the ",7" for field length. I was trying to simplify the problem. Thanks to everyone who responded.
The actual issue is a bit more complicated.
here is an example of the formula I am trying to enter
sum(if(find("CAPITAL",UCASE(A282:A290)>0,B282:B290,0))
that is, sum the amounts in cells B282 through B290 when the corresponding value in A282 through A290 contains "CAPITAL" (ignoring case)
inserting a @ sign did not work
The actual issue is a bit more complicated.
here is an example of the formula I am trying to enter
sum(if(find("CAPITAL",UCASE(A282:A290)>0,B282:B290,0))
that is, sum the amounts in cells B282 through B290 when the corresponding value in A282 through A290 contains "CAPITAL" (ignoring case)
inserting a @ sign did not work
- Try this:
=SUMPRODUCT(--(ISNUMBER(SEARCH("CAPITAL",A282:A290))),B282:B290)