# 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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### 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
13
Views
394
Replies
4
Views
120
Replies
5
Views
194
Replies
3
Views
86
Replies
7
Views
137