SUM Referenced Range where 3 sets of criteria met

urban_player

New Member
Joined
Feb 22, 2019
Messages
14
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,713
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
14
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
14
=SUM(SUMIFS(DaysOpen,DateLogged,">=" &C107,DateLogged,"<=" &D107,IncStatus,"Closed"))
^ Feb-19 ^ Mar-19
 

urban_player

New Member
Joined
Feb 22, 2019
Messages
14

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
38,775
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
14
Mr RoryA,

Thanks again for your input.

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

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

+1
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,279
Members
430,201
Latest member
Deepakpilla36

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top