我有一张像上图所示的桌子.还有另一张纸根据此数据报告一些信息.给定一个月的最后一周,我需要苹果的总和.
I have a table like the image given above. There is another sheet which reports some information based on this data. I need the sum of Apples in the last week for a given month.
假设还有另一条记录-苹果-32-10-8,则苹果第8个月的答案为14.
The answer for apples for 8 th month would be 14 assuming there is another record as Apples - 32 - 10 - 8.
所以我必须找到给定月份的最大周数,然后将具有最大周数的苹果的数量相加
So I would have to find max week number for a given month and then sum the quantity for Apples with max week number
写的公式是= SUMIFS(C2:C300,A2:A300,H16,B2:B300,MAX(IF(D2:D300 = MONTH(TODAY()-1),B2:B300)))
The formula written is =SUMIFS( C2:C300, A2:A300, H16, B2:B300, MAX(IF(D2:D300=MONTH(TODAY()-1),B2:B300)))
H16包含苹果"一词
H16 contains the word "Apples"
A列-项目B列-周C列-数量D列-月H栏-再次显示唯一商品".
Column A - Item Column B - Week Column C - Quantity Column D - Month Column H - Unique Items again..
MAX(IF(D2:D300 = MONTH(TODAY()-1),B2:B300))返回第8个月的正确最大值为32,但公式返回的值为0.在过时的单元格中使用最大公式,并在主公式中引用该单元格,它可以按预期工作.我不确定为什么不能将最大值的结果用于sumif函数.
MAX(IF(D2:D300=MONTH(TODAY()-1),B2:B300)) returns the correct max as 32 for month 8 but the formula returns the value as 0. If I put the max formula in aseperate cell and refer that cell in main formula, it works as expected. I am not sure why the result of max value is not being used for sumif function.
推荐答案您的公式很好.您需要做的就是将其转换为数组公式.只需使用 Ctr + shift + enter 输入公式.
Your formula is good. all you need to do is to convert it into Array Formula. Just type the formula with Ctr + shift + enter.
{= SUMIFS(C2:C300,A2:A300,H16,B2:B300,MAX(IF(D2:D300 = MONTH(TODAY()-1),B2:B300))))}
实际上,如果没有数组,则部分 IF(D2:D300 会得出#value错误,因为Excel不知道如何将一个数组与一个值进行比较.
Actually, without array, the part IF(D2:D300 evaluates to #value error as Excel doesn't not know how to compare an array against one value.
提示:不确定是否已经知道,但是无论如何,请始终使用公式->评估公式来查明公式中的错误.
Hint: Not sureif you know it already, but anyways, always use Formulas --> Evaluate Formula to pin-point an error in a formula.
更多推荐
在SumIFS Excel中使用Max函数结果
发布评论