Excel Sumif，多列中有部分字符串的Sumifs? [英] Excel Sumif, Sumifs with partial strings in multiple columns?

问题描述

这是我从以前在这里遇到的一个简单问题中分解出来的简化问题:

So this is the simplified question I broke down from a former question I had here: Excel help on combination of Index - match and sumifs? .

对于这个，我有Table1(黑色灰色)，其中有两列或更多列用于调整各种订单号.参见下面的图片:

For this one, I have Table1 (the black-gray one) with two or more columns for adjustments for various order numbers. See this image below:

我想要实现的是对那些订单编号进行总调整，这些订单编号包含蓝色表格中总调整"列中的编号，每个订单编号都取决于旁边的单元格.

What I want to achieve is to have total adjustments for those order numbers that contain the numbers in Total Adjustment column in the blue table, each of which will depend on the cell beside it.

示例:订单号17051有两种产品:17051A(苹果)和17051B(橙色).

Example: Order number 17051 has two products: 17051A (Apple) and 17051B (Orange).

现在我要在单元格C10中实现的是17051A和17051B的调整总和，它们将是:Apple调整(5000)+橙色调整(4500)= 9500.

Now what I want to achieve in cell C10 is the sum of adjustment for both 17051A and 17051B, which will be: Apple Adjustment (5000) + Orange Adjustment (4500) = 9500.

我在下面(和在图片中)使用的公式不断给我错误消息，即使在为Orange添加调整值之前，这种情况也会发生.

The formula I used below (and in the image) kept giving me error messages, and this happens even before I add the adjustment for Orange.

=SUMIF(Text(LEFT(Table1[Order Number],5),"00000"),text(B10,"00000"),Table1[Apple Adjustment])

我整天都在寻找解决方案，甚至没有找到任何解决方案.任何建议表示赞赏.

I have spent the whole day looking for a solution for this and didn’t even come close to find any. Any suggestion is appreciated.

推荐答案

假设您的标头中始终包含文本"adjustment"，则可以使用:

Assuming your headers always have the text "adjustment" in them, you could use:

=SUMPRODUCT((LEFT($B$4:$B$7,5)=B10&"")*(RIGHT($C$3:$F$3,10)="adjustment")*$C$4:$F$7)

这篇关于Excel Sumif，多列中有部分字符串的Sumifs?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！