+ Reply to Thread
Results 1 to 10 of 10

SUMIFS with matching Month

  1. #1
    Registered User
    Join Date
    12-08-2017
    Location
    Earth
    MS-Off Ver
    2010
    Posts
    5

    SUMIFS with matching Month

    Hello and good day,
    I am having much difficulty getting this to work, I did some digging around and perhaps it's not the right function or even possible, but I can't quite find the right direction.

    I have a table of transactions where column B will be dates such as "2017-09-01" and column H will be a transaction type. I would like to sum column F based on transaction type and date. Here are a few of the examples I have tried. The only way I can get it to sum is if I explicitly match the exact date. Example table below for clarity.

    B H F
    2017-09-01 Material 159.49
    2017-09-15 Material 2045.59
    2017-10-15 Labor 956.58

    =SUMIFS('2017'!F1:F100, '2017'!B1:B100, "September", '2017'!H1:H100, "Material") - Sums to 0
    =SUMIFS('2017'!F1:F100, MONTH('2017'!B1:B100), "September", '2017'!H1:H100, "Material") - Results in error
    =SUMIFS('2017'!F1:F100, MONTH('2017'!B1:B100), 09, '2017'!H1:H100, "Material") - Results in error
    =SUMIFS('2017'!F1:F100, '2017'!B1:B100, "*Sep*", '2017'!H1:H100, "Material") - Even a wildcard results in error

    I am not sure what else to do... any advice very much appreciated.
    Last edited by vidus; 12-08-2017 at 08:53 PM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: SUMIFS with matching Month

    Hi Vidus,

    If the first formula works, change to =SUMIFS('2017'!F:F,'2017'!B:B,"2017-09-01",'2017'!H:H,"Material")

    Other than that suggestion, maybe you could provide a sample sheet to work with.
    Last edited by jeffreybrown; 12-08-2017 at 08:39 PM.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    12-08-2017
    Location
    Earth
    MS-Off Ver
    2010
    Posts
    5

    Re: SUMIFS with matching Month

    Thanks for the reply jeffreybrown.

    The sample table is pretty much exactly the data I am working with.
    The problem with the first formula is that I was just using it as a test, I am looking to sum all values in a particular month, not a specific date. Perhaps I should have left that example out.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: SUMIFS with matching Month

    Sorry my brain wasn't engaged.

    Try this...

    Please Login or Register  to view this content.
    The answer I get for you small sample data is 2205.08

    You can't use the month calculation inside of the Sumifs and you shouldn't use the entire range in a Sumproduct

  5. #5
    Registered User
    Join Date
    12-08-2017
    Location
    Earth
    MS-Off Ver
    2010
    Posts
    5

    Re: SUMIFS with matching Month

    I just get #VALUE! from this formula. It appears very complex so I am not quite sure what to look for.

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: SUMIFS with matching Month

    Can you post a sample worksheet?

  7. #7
    Registered User
    Join Date
    12-08-2017
    Location
    Earth
    MS-Off Ver
    2010
    Posts
    5

    Re: SUMIFS with matching Month

    This is very strange, I created a sample workbook using actual data, and the formula did not function until I manually entered another line item... (in red). If you take that away the formula results in #VALUE!

    I checked the formatting, seems ok...
    Attached Files Attached Files

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: SUMIFS with matching Month

    It's because when you empty F8 and H8, the ranges become unequal. By your sample you led me to believe every cell was filled with a value.

    Try...

    Please Login or Register  to view this content.
    ...Which uses column B to establish the length of the column. To test, delete F8 and H8 and now you get 4323.45

  9. #9
    Registered User
    Join Date
    12-08-2017
    Location
    Earth
    MS-Off Ver
    2010
    Posts
    5

    Re: SUMIFS with matching Month

    This seems to work! I'll keep playing with it to apply it to the entire sheet. I wish I knew what was going on with this formula, I like to understand it but I can't figure out what is going on with the 9.9999 etc.
    Thank you so much!

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: SUMIFS with matching Month


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Sumifs with mid corresponding to the month
    By silambarasan.J in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-26-2017, 06:01 AM
  2. SUMIFS using Month
    By RadianWear in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-16-2016, 09:23 PM
  3. Sumifs with a month
    By pbt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-20-2014, 04:30 PM
  4. Matching, IF, SUMIFS, ISSUE- Help
    By exclusiveicon in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-16-2014, 02:41 PM
  5. SUMIFS used with month ?
    By spookiepower in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-29-2013, 06:44 AM
  6. Help With SUMIFS by Month
    By dsarkissian in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2012, 08:48 PM
  7. Excel 2007 : SUMIFS & month value
    By 78Idyll in forum Excel General
    Replies: 2
    Last Post: 11-22-2011, 05:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1