CODEWITHSUNDEEP
excel
yuro
yuro

Reputation: 2209

How to define min and max with sumifs() in Excel?

I have a problem and looking for a smart solution.

In my excel sheet, I defined the following columns with values:

CalcSheet: enter image description here

MasterSheet:

enter image description here

I'm working with the following function in Column C of MasterSheet:

    =SUMIFS(
        CalcSheet!D:D;
        CalcSheet!A:A; A4;
        CalcSheet!E:E; E4;
        CalcSheet!B:B; "<="&B4;
        CalcSheet!C:C;">"&B4
    )

How can I define the case, when an ID don't have a max value? When I let the cell empty, I get the value 0. So that my solution for the moment is to set the Max value to a bigger value then the Volume is. But it isn't a nice solution.

EDIT:

CalcSheet:

enter image description here

MasterSheet:

enter image description here

Upvotes: 0

Views: 2019

Answers (1)

Slai
Slai

Reputation: 22876

If you leave the Max value cell empty, then you can have OR condition like this:

= SUMIFS( CalcSheet!D:D; CalcSheet!A:A; A4; CalcSheet!E:E; E4; CalcSheet!B:B; "<="&B4; CalcSheet!C:C;">"&B4 )
+ SUMIFS( CalcSheet!D:D; CalcSheet!A:A; A4; CalcSheet!E:E; E4; CalcSheet!B:B; "<="&B4; CalcSheet!C:C;"" )

or with one SumProduct formula:

= SumProduct( CalcSheet!D:D * ( CalcSheet!A:A = A4 ) * ( CalcSheet!E:E = E4) *
  ( CalcSheet!B:B <= B4 ) * ( ( CalcSheet!C:C > B4 ) + ( CalcSheet!C:C = "" ) ) )

Upvotes: 1

Related Questions