Taking sum of HP. But my formula is providing me wrong amount despite of criteria not being fulfilled!

CodePudding user response：

I think it's worth posting an explanation as to why your original formula does not work, as it highlights an interesting feature of the function SUMIF.

As stated in one of my commments, you are passing a range parameter to that function of CN_2021[[#All],[HP]:[Sum of ORDER_AMT]], which is a reference to the entire table (including headers). Hence, you are effectively searching for the entry from P14 in all three columns, not just the first.

However, you are also using a sum_range parameter of CN_2021[[#All],[Sum of ORDER_QTY]], which is only one column. One might think that the construction should flat out error, since your range and sum_range parameters are not of an equal dimension (the former comprises 3 columns, the latter only 1).

The reason it does not error is due to the fact that, in such cases, Excel redimensions the sum_range so as to be of an equal dimension to the range.

So, if we replace the Table references with their equivalent worksheet references (assuming that table begins in A3) for the sake of simplification, we have that

=SUMIF(CN_2021[[#All],[HP]:[Sum of ORDER_AMT]],P14,CN_2021[[#All],[Sum of ORDER_QTY]])

which is equivalent to

=SUMIF(A3:C19,P14,B3:B19)

should error, though the single-column range

B3:B19

is redimensioned to a three-column range in line with the range, i.e.

B3:D19

which means that you are effectively performing

=SUMIF(A3:C19,P14,B3:D19)

This could have potential consequences were column D also populated: for example, place a 3 in cell C14 and 1000 in cell D14. The formula will now pick up this extra 1000 in the sum.

Strangely, unlike SUMIF, SUMIFS does not appear to be so lenient with respect to this redimensioning:

=SUMIFS(B3:B19,A3:C19,P14)

errors.