SUMIF，如何仅基于单元格中的某些字母/数字求和? [英] SUMIF, how can I sum values based only on certain letters/numbers from cell?

问题描述

在范围A1:B25中，我有类别(A1:A25)和数字(B1:B25).A列中的单元格包含用逗号"1,2,3,11,1056,392"等分隔的数字.

In my range A1:B25 I have categories (A1:A25) and numbers (B1:B25). The cells in column A contains numbers seperated by comma "1,2,3,11,1056,392" etc.

此公式:

= SUMPRODUCT(ISNUMBER(FIND(，"& 1&，";，& SUBSTITUTE(A1:A25;";")&"，))**1)

正在计算所有包含数字"1"但不包括"11"，"101"等的单元格.

is counting all the cells which contains the number "1" and excludes "11", "101" etc.

完美！

但是我正在寻找一个公式，该公式为我在A列中提供包含"1"的单元格，然后将B列中所有对应的单元格求和.

But I'm looking for a formula that gives me the cells containing "1" in column A and then sums all the corresponding cells in column B.

像这样:

= SUMIF(，"& A1:A25&，";，"& C1&，"; B1:B25)

C1是我的核对值("1")

C1 is my check-up-value ("1")

我该如何进行这项工作?

How can I make this work?

推荐答案

假定A列中的所有数字都仅用逗号分隔，则此公式应能解决问题:

Assuming all your numbers in column A are separated by only commas, This formula should do the trick:

=SUMPRODUCT(--(ISNUMBER(FIND(",1,",","&A1:A25&","))),B1:B25)

希望这会有所帮助！

这篇关于SUMIF，如何仅基于单元格中的某些字母/数字求和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！