SUM with conditions but look at date

Solved/Closed
Gouws Posts 45 Registration date Sunday February 7, 2010 Status Member Last seen April 15, 2012 - Aug 18, 2010 at 12:59 AM
Gouws Posts 45 Registration date Sunday February 7, 2010 Status Member Last seen April 15, 2012 - Aug 19, 2010 at 07:11 AM
Hello,
I want to sum the conditions in a certain month. Say for June from the 1st to30th or July 1 to 31. The formula that is used without the date is: {=SUM(IF((Database!$C$2:Database!$C$1171="Gate")*(Database!$E$2:Database!$E$1171="Incident"),Database!$M$2:Database!$M$1171))}. The date and time is in column A (02/07/2010 17:00 )(Custom dd/mm/yyyy hh:mm). How do i change the formula so that it looks at a specified month.
The data can be filtered but i want it to SUM specific months even if data is filtered.

Excel 2007

1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 18, 2010 at 07:25 AM
I think you can do extract month too. I have not tested it, see if it works

SUM(IF((Database!$C$2:Database!$C$1171="Gate")*(Database!$E$2:Database!$E$1171="Incident") * (MONTH(Database!$A$2:Database!$A$1171) = 2),Database!$M$2:Database!$M$1171))
1
Gouws Posts 45 Registration date Sunday February 7, 2010 Status Member Last seen April 15, 2012
Aug 19, 2010 at 07:11 AM
TX, riviza tested it and it work! Also used
=SUM(IF((Database!$A$2:Database!$A$1999>B22)*(Database!$A$2:Database!$A$1999<B24)*(Database!$C$2:Database!$C$1999="HardAsh")*(Database!$E$2:Database!$E$1999="RunHard"),Database!$M$2:Database!$M$1999))
I made a list with date and used say 31/07/2010 23:59 to 31/08/2010 23:59 and it also works.
0