Sumifs with nested Or, & comparative operators

[CLove511]

I currently have an issue with sumifs:

I need to sum a range if:
-The name matches
-The add date is less than the start date
-the disposal date either = "" OR >= the start date

It took me forever to learn this little bit: ">=" & D3 for the criteria.
I just read about making an array for the OR: {1,2;"AB32","A12"}

But how do I combine them?? The array highlights all quotation marks around comparative operators as errors (">=")

This is what I have so far: =SUMIFS('FA Detail'!$D$8:$D$370,'FA Detail'!$C$8:$C$370,Rollforward!$C5,'FA Detail'!$E$8:$E$370,"<="&$D$3,'FA Detail'!$F$8:$F$370,{"",">="&$D$3})

where the columns on the other sheet are C:NAME, D:PRICE, E:ADD DATE, F:DISPOSAL DATE; $D$3 is the start date, and $C5 is the name to match

 

[svendiamond]

Try this:

=SUMPRODUCT(--('FA Detail'!$C$8:$C$370=Rollforward!$C5)*--('FA Detail'!$E$8:$E$370<=$D$3)*('FA Detail'!$F$8:$F$370="")+('FA Detail'!$F$8:$F$370>=$D$3),'FA Detail'!$D$8:$D$370)

 

[Chrisdontm]

I currently have an issue with sumifs:



I came up with this:

=SUMIFS('FA Detail'!$D$8:$D$370,'FA Detail'!$C$8:$C$370,Rollforward!C5,'FA Detail'!$E$8:$E$370,"<="&'FA Detail'!$D$3,'FA Detail'!$F$8:$F$370,">="&'FA Detail'!$D$3)

☺

 

[svendiamond]

Yeah but if $F$8:$F$370 is blank ("") he still wants to count it... that was the problem... right?

 

[Aladin Akyurek]

@CLove511

Assuming that the formula is in the sheet called Rollforward...

=SUMPRODUCT(
    SUMIFS(
      'FA Detail'!$D$8:$D$370,
      'FA  Detail'!$C$8:$C$370,$C5,
      'FA  Detail'!$E$8:$E$370,"<="&$D$3,
      'FA Detail'!$F$8:$F$370,CHOOSE({1,2},"",">="&$D$3)))

Does this give you what you need?

 

[svendiamond]

Hey Aladin,

Can you explain how the CHOOSE function is working here? I thought it was to choose an action to perform based on the index number... are 1 and 2 the index numbers? What exactly do they do?

 

[Aladin Akyurek]

Hey Aladin,

Can you explain how the CHOOSE function is working here? I thought it was to choose an action to perform based on the index number... are 1 and 2 the index numbers? What exactly do they do?

CHOOSE is given {1,2} as choices to execute, that is, both 1 and 2, which forces the function to construct an array constant consisting of two values.

Let D3 = 10.

CHOOSE({1,2},"",">="&$D$3)

will deliver:

{"",">=10"}

SUMIFS by ist design can interpret as intended. Note that we need SUMPRODUCT around SUMIFS in order to sum the two values the interpretation generates.

 

[CLove511]

Thanks so much for your help!!

This did the trick:

=SUMIFS('FA Detail'!$D$8:$D$373,
         'FA Detail'!$C$8:$C$373,C5,
         'FA Detail'!$E$8:$E$373,"<="&$D$3,
         'FA Detail'!$F$8:$F$373,CHOOSE({1,2},"",">="&$D$3))

I didn't understand the SUMPRODUCT, so I cut it out, and it got exactly what I was looking for. The CHOOSE is going to be my go-to solution for this now.

 

[Aladin Akyurek]

Thanks so much for your help!!

Glad to help.

This did the trick:

=SUMIFS('FA Detail'!$D$8:$D$373,
         'FA Detail'!$C$8:$C$373,C5,
         'FA Detail'!$E$8:$E$373,"<="&$D$3,
         'FA Detail'!$F$8:$F$373,CHOOSE({1,2},"",">="&$D$3))

I didn't understand the SUMPRODUCT, so I cut it out, and it got exactly what I was looking for. The CHOOSE is going to be my go-to solution for this now.

SUMIFS creates a sum for each criterion it cannot total itself. Hence surrounding SUMPRODUCT which can do just that.