在SumIFS Excel中使用Max函数结果 [英] Using Max function result in SumIFS Excel

问题描述

我有一张像上图所示的桌子.还有另一张纸根据此数据报告一些信息.给定一个月的最后一周，我需要苹果的总和.

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函数结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！