# SUM Referenced Range where 3 sets of criteria met

#### urban_player

##### New Member
Hi Hi,

I have three referenced ranges.

1) DaysOpen
2) DateLogged
3) IncStatus

I am trying to work out the total sum of DaysOpen where the following conditions are met:

1) If the cell date is between two dates using referenced cells
DateLogged >= C8 & DateLogged =<D8
C8 = Date Cell (Feb-19)
D8 = Date Cell (Mar-19)

2) If the IncStatus range (column) contains cell with "Closed"

I have tried the following:

=SUM(SUMIFS(DaysOpen,DateLogged,">=" &C107,DateLogged,"<=" &D107,IncStatus,"Closed"))
=IF(DateLogged>=C107,IF(DateLogged<=D107,IF(IncStatus="Closed",SUM(DaysOpen)*1,0)*0,)*0,) - it auto corrected me with the asterisk (dont ask )

I don't know if the referenced ranges need to be an exact match in terms of row/column size, a problem with the cell format (date type) or anything else.

There are some good resources online but i'm still struggling with what i would call a simple query (which i should know how to do by now!)

urban_player

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### steve the fish

##### Well-known Member
Could you explain more clearly what this means?

1) If the cell date is between two dates using referenced cells
DateLogged >= C8 & DateLogged = C8 = Date Cell (Feb-19)

#### urban_player

##### New Member
Could you explain more clearly what this means?

'ello Steve

DateLogged is a referenced range (1 column) of cells which contain dates in the format of "02/02/2019".

If DateLogged is => Feb-19 & DateLogged is =< Marc-19

I have Feb-19 and Mar-19 as a referenced cell in terms of lookup.

=SUM(SUMIFS(DaysOpen,DateLogged,">=" &C107,DateLogged,"<=" &D107,IncStatus,"Closed"))
^ Feb-19 ^ Mar-19

Hope that is more clear now

#### urban_player

##### New Member
=SUM(SUMIFS(DaysOpen,DateLogged,">=" &C107,DateLogged,"<=" &D107,IncStatus,"Closed"))
^ Feb-19 ^ Mar-19

#### urban_player

##### New Member
Can't edit posts:

Edit:

=SUM(SUMIFS(DaysOpen,DateLogged,">=" &C107,DateLogged,"<=" &D107,IncStatus,"Closed"))

C107 = Feb-19
D107 = Mar-19

#### RoryA

##### MrExcel MVP, Moderator
You don't need an additional SUM function there:

=SUMIFS(DaysOpen,DateLogged,">=" &C107,DateLogged,"<=" &D107,IncStatus,"Closed")

should suffice. The three named ranges need to be exactly the same size.

#### urban_player

##### New Member
Mr RoryA,

I made sure all ranged references were same size, using \$.

All working ))))))))))

+1

Replies
1
Views
255
Replies
3
Views
80
Replies
1
Views
285
Replies
5
Views
441
Replies
5
Views
226

1,195,663
Messages
6,011,009
Members
441,579
Latest member
satishrazdhan

### 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