# Sumifs with nested Or, & comparative operators

#### CLove511

##### New Member
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.

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

Last edited:

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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)

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)

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

@CLove511

Assuming that the formula is in the sheet called Rollforward...
Rich (BB code):
``````
=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?

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?

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.

Thanks so much for your help!!

This did the trick:
Code:
``````=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.

Last edited:
Thanks so much for your help!!

This did the trick:
Code:
``````=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.

Replies
0
Views
595
Replies
7
Views
257
Replies
5
Views
159
Replies
1
Views
168
Replies
0
Views
294

1,196,412
Messages
6,015,113
Members
441,871
Latest member
lajervik

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back