Jump to content


Photo

sumifs question


  • Please log in to reply
2 replies to this topic

#1 Level43

Level43

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 05 April 2018 - 01:33 AM

Any help would be appreciated.  Thanks! (see attached file).Attached File  ExcelCentralSumifs.xlsx   9.17KB   39 downloads



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 05 April 2018 - 10:31 AM

Hi Level43,

 

The formula you are looking for is something like (in cell H4):

=SUMIFS($E$4:$E$9,$C$4:$C$9,">="&G4,$C$4:$C$9,"<"&G5)

 

This SUMIFS formula checks whether the date is greater than or equal to the month in cell G4 (January 2018) and whether the date is less than the date in cell G5 (February 2018).

 

You can see more about SUMIFS in: Expert Skills Session 3: Advanced Functions (Lesson 3-6: Use the SUMIF and COUNTIF functions to create conditional totals).

 

You'll notice that the ampersand (&) has been used to concatenate the logical functions >= and < with the cell references G4 and G5. This is needed because Excel will not understand >=G4 or ">=G4" in this context. You can see more about using the ampersand to concatenate text in: Expert Skills Session 3: Advanced Functions (Lesson 3-18: Concatenate strings using the concatenation operator (&)).

 

You'll also notice absolute cell references like $E$4. These aren't strictly necessary, but they make it possible to AutoFill the formula down without needing to adjust it for each individual row. You can see more about absolute and relative cell references in: Essential Skills Session 3: Taking Your Skills To The Next Level (Lesson 3-12: Understand absolute and relative cell references).

 

I have attached a copy of your workbook with the formulas applied:

 

Attached File  ExcelCentralSumifs.xlsx   9.57KB   25 downloads

ExcelCentralSumifs.png

 

I hope this is the solution you were looking for, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Level43

Level43

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 05 April 2018 - 01:24 PM

Thank you very much! I appreciate your explanation.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users