• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating total of values if more than one in a single cell, separated by comma.

V

VDS

Member
Dear All,

I have created an excel file of Column A "Package Nos" abd Column B" Total" like the following :-


PACKAGES TOTAL


121,125,140 =(LEN(A1)-LEN(SUBSTITUTE(A1, ",",)))/1+1 = 3
135,18 =(LEN(A2)-LEN(SUBSTITUTE(A2, ",",)))/1+1 = 2

GRAND TOTAL IS 5


Here, by this formula, total comma is taken among the numbers +1 to know total packages of each row. Then Grand Total

Total rows would be around 100+

Here, I want to delete the Column B and grand total should be displayed below Column A with a single formula

How it can be done. Help is requested.


VDS
 
Dear All,

Thanks for reply.

But result is not accurate. Where it is incorrect ?

Attach herewith the sample excel file.



VDS
 

Attachments

Hi ,

You should be aware that blank cells can affect the result , which is why Nebu's formula , which considers blanks , is the correct one.

The equivalent one using SUM and CTRL SHIFT ENTER would be :

=SUM(IF(LEN(A1:A100)>0,LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,",",""))+1))

Narayan
 
You can use this array formula as well
=SUMPRODUCT(--(A3:A26<>""),(LEN(A3:A26)+1)-LEN(SUBSTITUTE(A3:A26,",",""))) Do not forget the negation part highlighted in Red. Last time you forgot to put the negation hence the sum was zero.

Thanks
 
The array formulas given by Hui modified to suit your requirement
=SUM((LEN(A3:A26)+1)-LEN(SUBSTITUTE(A3:A26,",","")))
=SUM((LEN(A3:A26)+1)-LEN(SUBSTITUTE(A3:A26,",","")))/LEN(",")
Thanks
 
@ Dear All,

Thanks a lot for your replies/brilliant answers. Now the query is solved.

In fact, this has even solved one of my another query. Great work indeed.

VDS
 
You must log in or register to reply here.
Back
Top