Find() nested in Sumproduct (Array Formulas)

[bruderbell]

I'm trying to come up with a formula to conditionally sum the parts of cells in an array.

Column A
A-8
x
x
x
S-2
s-1.75
a-9.5

So I'm trying to find the sum of values that begin only with s, either caps or not.
I think this'll be a sumproduct, but I am running into trouble when I try to insert a FIND in the formula

I think one of the arrays will be this: --(UPPER(LEFT(A1:A7,1)))="S"
That returns a nice 0 or 1. So if I can just get an array to return all of the numbers to the right of the "-" I'll be in business. So it seems like I should make my second array:
RIGHT(A1:A7,LEN(A1:A7)-FIND("-",A1:A7))
But not every value in the array has a dash, so the FIND is throwing #VALUE!. I thought of using MID(A1:A7,2) but that throws an error because not every field has a second character. And when I add IFERROR it doesn't help either one. Here's what I tried for the second array with an IFERROR:
RIGHT(A1:A7,LEN(A1:A7)-IFERROR(FIND("-",A1:A7),0))
It seems that the FIND is throwing a single #VALUE! instead of trying each value and throwing an array of valid responses and errors.
Any ideas on making this scenario work? I'd like to solve it formulaically and avoid creating extra intermediary cells.

Thanks!

 

[Aladin Akyurek]

Try...

C1: s

C2, control+shift+enter, not just enter:

=SUM(IF(LEFT(A1:A7,1)=C1,REPLACE($A$1:$A$7,1,SEARCH(C1&"-",$A$1:$A$7)+1,"")+0))

 

[Beate Schmitz]

Hello Aladin,

I'm following this thread. And I consider your solution as fine for the demand. But I tried to replace SEARCH in the formula by FIND and then I got the result: #Name!

How is the formula to be amended when I search for the sum of S or s?

 

[Domenic]

Replacing SEARCH with FIND should not result in the error #NAME!. Maybe the formula containS a typo. For a case-sensitive based result, try...

=SUM(IF(EXACT(LEFT(A1:A7,1),C1),REPLACE($A$1:$A$7,1,SEARCH(C1&"-",$A$1:$A$7)+1,"")+0))

...confirmed with CONTROL+SHIFT+ENTER.

 

[pgc01]

But not every value in the array has a dash, ...

Assuming the string in C1 may or may not be followed be the "-", try:

=SUM(IF(LEFT(A1:A7,1)=C1,REPLACE($A$1:$A$7,1,LEN(C1)+(MID($A$1:$A$7,LEN(C1)+1,1)="-"),"")+0))

or, case sensitive:

=SUM(IF(EXACT(LEFT(A1:A7,1),C1),REPLACE($A$1:$A$7,1,LEN(C1)+(MID($A$1:$A$7,LEN(C1)+1,1)="-"),"")+0))

 

[Beate Schmitz]

Thank you both Dominic and PGC for your replies.

With Dominics formula the result was only the decimals.

With PGC formula the result was correct but always with minus-sign at the beginning.

After amending the forumula this way, I achieve the correct results for case sensitive:

=SUM(IF(EXACT(LEFT(A1:A7,1),C1),REPLACE($A$1:$A$7,1,LEN(C1)+(MID($A$1:$A$7,LEN(C1)+2,1)="-"),"")+0))
Today 12:12 AM

 

[pgc01]

With PGC formula the result was correct but always with minus-sign at the beginning.

=SUM(IF(EXACT(LEFT(A1:A7,1),C1),REPLACE($A$1:$A$7,1,LEN(C1)+(MID($A$1:$A$7,LEN(C1)+2,1)="-"),"")+0))

Hi Beate

Thank you for posting the corrected version.

 

[bruderbell]

Thanks all for the help. PGC's formula has the desired effct for my scenario, as the "dashes" are present in some cells but do not indicate negative values for this spreadsheet. I'll try to dissect the formula to be able to contribute to the forum in the future on this type of question!