如何查找特定单元格中某个数字范围内的值的总数? [英] How to find total number of values in a number range in a particular cell?

问题描述

例如:

数据列:23至26、40、22、7、27至28、49、50至51、48、136、169、190至192、17至22和无

*包括单个数字，数字范围和单词.

我在每个单元格中都有如上所示的这些数据，如何得出一个将所有值总数相加的公式?

我需要对数据进行一些更新.

我目前正在使用 Jerry Beaucaire 提供的公式:

  = ROWS(INDIRECT(SUBSTITUTE(SUBSTITUTE(A1，-"，:")，"TO"，:")))) 

谢谢！

解决方案

尝试使用基于 JMAX的方法的此公式

= SUM(IF(ISERR(FIND("TO"，A1:A6))，ISNUMBER(A1:A6)+ 0，MID(A1:A6，FIND("TO"，A1:A6)+2,9)-LEFT(A1:A6，FIND("TO"，A1:A6)-1)+1)))

已通过 CTRL + SHIFT + ENTER

确认

根据您的示例，假定分隔符为"TO"

修订方法:

FIND区分大小写，而SEARCH不区分大小写，因此要允许"TO"或"to"，您可以将FIND的所有实例都更改为SEARCH.因此，以下版本将处理以下任何一个:空白单元格，带有"Nil"的单元格，具有诸如23、24、27(计数为3)或11和11等条目的单元格.99(计数为2)或具有单个数字的单元格(例如33)(计数为1)

= SUM(IF(ISERR(SEARCH("TO"，A1:A6))，IF((A1:A6 ="Nil")+(A1:A6 =")，0，LEN(A1:A6)-LEN(SUBSTITUTE(SUBSTITUTE(A1:A6，，"，")，&"，"))+ 1)，MID(A1:A6，SEARCH("TO"，A1:A6)+2,9)-LEFT(A1:A6，SEARCH("TO"，A1:A6)-1)+1))

第2次修订

假设数据在A1:A22

= SUM(IF(ISNUMBER(LEFT(A1:A22)+0)，IF(ISNUMBER(SEARCH("to"，A1:A22))，MID(A1:A22，SEARCH("TO"，A1:A22)+2,9)-LEFT(A1:A22，SEARCH("TO"，A1:A22)-1)+ 1，LEN(A1:A22)-LEN(SUBSTITUTE(SUBSTITUTE(A1:A22，，"，")，&"，"))+ 1))))

For example:

DATA COLUMN: 23 TO 26, 40, 22, 7, 27 TO 28, 49, 50 TO 51, 48, 136, 169, 190 TO 192, 17 TO 22 AND NIL

*includes individual numbers, number range and word.

I have these datas as shown above in each individual cell, how do I come up with a formula that sums up all the total number of values?

I need to do some updating of datas.

I am currently using a formula provided by Jerry Beaucaire:

=ROWS(INDIRECT(SUBSTITUTE(SUBSTITUTE(A1," - ",":")," TO ", ":")))

Thanks!

解决方案

Try this formula based on JMAX's approach

=SUM(IF(ISERR(FIND("TO",A1:A6)),ISNUMBER(A1:A6)+0,MID(A1:A6,FIND("TO",A1:A6)+2,9)-LEFT(A1:A6,FIND("TO",A1:A6)-1)+1))

confirmed with CTRL+SHIFT+ENTER

assumes delimiter is "TO" as per your examples

Revised approach:

FIND is case-sensitive, SEARCH is not, so to allow "TO" or "to" you can change all instances of FIND to SEARCH. The following version will therefore cope with either of those, blank cells, cells with "Nil", cells with entries such as 23, 24, 27 (counted as 3) or 11 & 99 (counted as 2) or cells with a single number like 33 (counted as 1)

=SUM(IF(ISERR(SEARCH("TO",A1:A6)),IF((A1:A6="Nil")+(A1:A6=""),0,LEN(A1:A6)-LEN(SUBSTITUTE(SUBSTITUTE(A1:A6,",",""),"&",""))+1),MID(A1:A6,SEARCH("TO",A1:A6)+2,9)-LEFT(A1:A6,SEARCH("TO",A1:A6)-1)+1))

Revision no 2

Assuming data in A1:A22

=SUM(IF(ISNUMBER(LEFT(A1:A22)+0),IF(ISNUMBER(SEARCH("to",A1:A22)),MID(A1:A22,SEARCH("TO",A1:A22)+2,9)-LEFT(A1:A22,SEARCH("TO",A1:A22)-1)+1,LEN(A1:A22)-LEN(SUBSTITUTE(SUBSTITUTE(A1:A22,",",""),"&",""))+1)))

这篇关于如何查找特定单元格中某个数字范围内的值的总数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！