SUM Referenced Range where 3 sets of criteria met

urban_player

New Member
Joined
Feb 22, 2019
Messages
12
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 :LOL: )

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
 

Some videos you may like

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
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 22, 2019
Messages
12
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

New Member
Joined
Feb 22, 2019
Messages
12
=SUM(SUMIFS(DaysOpen,DateLogged,">=" &C107,DateLogged,"<=" &D107,IncStatus,"Closed"))
^ Feb-19 ^ Mar-19
 

urban_player

New Member
Joined
Feb 22, 2019
Messages
12

ADVERTISEMENT

Can't edit posts:

Edit:

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

C107 = Feb-19
D107 = Mar-19
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,513
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Feb 22, 2019
Messages
12
Mr RoryA,

Thanks again for your input.

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

All working :)))))))))))

+1
 

Watch MrExcel Video

Forum statistics

Threads
1,109,161
Messages
5,527,156
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top