SUM Referenced Range where 3 sets of criteria met

[urban_player]

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!)

Thanks for reading and your time.

urban_player

 

[steve the fish]

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]

Could you explain more clearly what this means?

'ello Steve

Thanks for your reply.

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]

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

 

[urban_player]

Can't edit posts:

Edit:

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

C107 = Feb-19
D107 = Mar-19

 

[RoryA]

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]

Mr RoryA,

Thanks again for your input.

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

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

+1