admin管理员组

文章数量:1122833

I specify a SUMIFS formula that checks for a criteria using a reference. For this example, i use =SUMIFS(B:B; C:C; "*" & F3 & "*") (F4 in the second example). Provided picture shows the result of 2 formulas that calculate the SUM in column E.

I cant figure out why the SUM for CRITERIA 2030 does not add up to $15 and also why the SUM for CRITERIA 2031 does not add up to $55.

The use of wildcards (*) specifically states that it checks for any length including 0 characters.

Currently i work around this problem by using =SUM(SUMIFS(B:B; C:C; "*" & F3 & "*"); SUMIFS(B:B; C:C; F3)) but it does not sit right with me.

Any ideas?

I specify a SUMIFS formula that checks for a criteria using a reference. For this example, i use =SUMIFS(B:B; C:C; "*" & F3 & "*") (F4 in the second example). Provided picture shows the result of 2 formulas that calculate the SUM in column E.

I cant figure out why the SUM for CRITERIA 2030 does not add up to $15 and also why the SUM for CRITERIA 2031 does not add up to $55.

The use of wildcards (*) specifically states that it checks for any length including 0 characters.

Currently i work around this problem by using =SUM(SUMIFS(B:B; C:C; "*" & F3 & "*"); SUMIFS(B:B; C:C; F3)) but it does not sit right with me.

Any ideas?

  • excel
  • excel-formula
Share Improve this question edited Nov 21, 2024 at 17:46 Mayukh Bhattacharya 27k8 gold badges29 silver badges42 bronze badges asked Nov 21, 2024 at 13:19 RAHRAH 412 silver badges8 bronze badges 0
Add a comment  | 

2 Answers 2

Reset to default 1

Like I said in the comments:

It seems that your criteria is only searching for text values, as 2030 and 2031 are numerical values, they don't show up. I got it working by formatting the cells as text, then editing them (add a space) and reversing the edit (remove the space).

I was too late to edit, but you could just double click or press F2 and enter each cell. However:

I got around this by using FILTER, ISNUMBER and SEARCH:

=SUM(FILTER(B:B;(ISNUMBER(SEARCH(F2;C:C)))))

A simple formula works

[E3]=SUM($B$3:$B$5*ISNUMBER(FIND(F3,$C$3:$C$5)))

Fill it down.

本文标签：

版权声明：本文标题：Excel Formula search criteria with a cell reference and wildcards (*) does not include the results where the cell only contains 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1736310528a1934409.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。