CODEWITHSUNDEEP
excelexcel-formula
codeEnthusiast
codeEnthusiast

Reputation: 311

Using Excel's SUMPRODUCT with SEARCH and ignoring blank cells

Suppose I have a row of cells that contain comma delimited strings like so:

enter image description here

I have figured out how to add the first character of each string using this formula:

=SUMPRODUCT(--(LEFT(C2:G2,SEARCH(",",C2:G2,1)-1)))

Now, I would like to extend the formula to the cell AA2, like so

=SUMPRODUCT(--(LEFT(C2:AA2,SEARCH(",",C2:AA2,1)-1)))

however, I cannot seem to get the it to ignore empty cells - it throws a #VALUE! error. I realize that I could simply update the formula each time I enter data in the subsequent cells, but that's not exactly efficient. I assume this throws an error because the SEARCH function returns a null value.

How might I get this to work?

Upvotes: 1

Views: 546

Answers (2)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

You can try below modified formula:

=SUMPRODUCT(--(LEFT(C2:AA2&"0,",SEARCH(",",C2:AA2&"0,",1)-1)))

For right side you can use MID like below and check:

=SUMPRODUCT(--(MID(C2:AA2&".0",SEARCH(",",C2:AA2&",.0",1)+1,99)+0))

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Your idea of searching for the comma is not needed if you only want to add the first character of each cell. Just grab the left-most single character.

Say we may have data from A1 to Z1 that may include some blank cells. Pick a cell and enter the array formula:

=SUM(IF(LEN(A1:Z1)>1,--LEFT(A1:Z1),0))

for example:

enter image description here

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

Upvotes: 0

Related Questions