looking to tally a number of rows in excel but most have a < symbol, looking for a formula that will tell excel to ignore it (i.e. something along the lines of =sum(d1:d8)substitute(D1:D8,"<","")) - thanks!
looking to tally a number of rows in excel but most have a < symbol, looking for a formula that will tell excel to ignore it (i.e. something along the lines of =sum(d1:d8)substitute(D1:D8,"<","")) - thanks!
Tryif does not work: Please read the yellow banner at the top of the page. Or skip to extended version of it below:Formula:Please Login or Register to view this content.
Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).
1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If there are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, empty cells between data, mixed text/numbers, etc. - please show them all, or at least indicate in text). The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).
3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!
To attach an Excel file you have to do the following (paperclip icon does not work!): Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.
Best Regards,
Kaper
=SUM(D1:D8) should ignore cells containing (only) < or > or any text
If it does not work please post a sample sheet see yellow banner
Thanks Kaper - unfortunately it didnt work.-, example is attached.
First: there are not < but <350 etc.
Second: What would be expected result? (column AP value?)
=SUM(A2:AO2) which is 58 is probably wrong
may be =COUNT(A2:AO2) which is 1 and which better suits to what I understand as tally is right
or … see point 2. in my post above
sorted it out: =SUMPRODUCT(--RIGHT(D8:AR8,LEN(SUBSTITUTE(D8:AR8,"<",""))))
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks