+ Reply to Thread
Results 1 to 11 of 11

SUMIFS used with month ?

  1. #1
    Registered User
    Join Date
    07-20-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2007
    Posts
    33

    SUMIFS used with month ?

    I need to sum all the numbers in J column, where the F column is "Solgt" and the month in column H is August.

    What I have done so far is this - but it does not work. I guess that it has something to do with how I used the month function.

    =SUMIFS(Sheet1!J:J;Sheet1!F:F;"Solgt";Sheet1!H:H;MONTH(8))

    My date is in this format - 01-12-2013

    Anyone who can help ?

    - Kim -

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMIFS used with month ?

    Is it just August in 2013? If so you can specify start and end date, e.g.

    =SUMIFS(Sheet1!J:J;Sheet1!F:F;"Solgt";Sheet1!H:H;">="&DATE(2013;8;1),Sheet1!H:H;"<"&DATE(2013;9;1))
    Audere est facere

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: SUMIFS used with month ?

    Perhaps..

    =SUMPRODUCT((Sheet1!F2:F1000="Solgt")*(MONTH(Sheet1!H2:H1000)=8)*(Sheet1!J2:J1000))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    07-20-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: SUMIFS used with month ?

    Quote Originally Posted by daddylonglegs View Post
    Is it just August in 2013? If so you can specify start and end date, e.g.

    =SUMIFS(Sheet1!J:J;Sheet1!F:F;"Solgt";Sheet1!H:H;">="&DATE(2013;8;1),Sheet1!H:H;"<"&DATE(2013;9;1))
    Yes, it is only august in 2013. I forgot to tell that

  5. #5
    Registered User
    Join Date
    07-20-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: SUMIFS used with month ?

    Thanks to both of you

    I'll take a look at it

    - Kim -

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: SUMIFS used with month ?

    This should work for you.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  7. #7
    Registered User
    Join Date
    07-20-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: SUMIFS used with month ?

    Quote Originally Posted by Fotis1991 View Post
    Perhaps..

    =SUMPRODUCT((Sheet1!F2:F1000="Solgt")*(MONTH(Sheet1!H2:H1000)=8)*(Sheet1!J2:J1000))
    Should I think of the * as an AND ? When I do some tests, it seems to me that it works like AND - just asking to be sure.

    - Kim -

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: SUMIFS used with month ?

    * is to multiply
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  9. #9
    Registered User
    Join Date
    07-20-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: SUMIFS used with month ?

    I'm a bit confused now. I know what multiplying is - but in this case I can't seem what multiplying have to do with this.

    Multiplying the word "solgt" with a month and then multiply with some numbers - does not make any sense to me.

  10. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMIFS used with month ?

    Yes, you are correct * gives an AND effect (+ gives an OR effect) but it does multiply. When you use a condition against a range like this

    (Sheet1!F2:F1000="Solgt")

    Then the result is an array of TRUE/FALSE values and when you multiply two or more of those arrays together the TRUEs becomes 1s and the FALSEs become zeroes so the resultant array has 1 and zero values, 1s only where all conditions are TRUE. If you then multiply that array by the "sum range" you will effectively sum rows only where all conditions are TRUE, so the effect is similar to SUMIFS

    see here for an explanation of SUMPRODUCT, particularly with regard to multi-conditional tests

  11. #11
    Registered User
    Join Date
    07-20-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: SUMIFS used with month ?

    Quote Originally Posted by daddylonglegs View Post
    Yes, you are correct * gives an AND effect (+ gives an OR effect) but it does multiply. When you use a condition against a range like this

    (Sheet1!F2:F1000="Solgt")

    Then the result is an array of TRUE/FALSE values and when you multiply two or more of those arrays together the TRUEs becomes 1s and the FALSEs become zeroes so the resultant array has 1 and zero values, 1s only where all conditions are TRUE. If you then multiply that array by the "sum range" you will effectively sum rows only where all conditions are TRUE, so the effect is similar to SUMIFS

    see here for an explanation of SUMPRODUCT, particularly with regard to multi-conditional tests
    Now I see what Alkey meant, when he told me that * is the same as multiplying. Thanks

    - Kim -

+ 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. Macros with SUMIFS that changes dependent on month
    By WadeLair in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-05-2013, 05:00 PM
  2. Help With SUMIFS by Month
    By dsarkissian in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2012, 08:48 PM
  3. Excel 2007 : SUMIFS & month value
    By 78Idyll in forum Excel General
    Replies: 2
    Last Post: 11-22-2011, 05:22 PM
  4. SUMIFS with MONTH() or other similar functions
    By e_lad in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-15-2011, 11:25 AM
  5. SUMIFS by month/year
    By Lyric in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-15-2011, 02:39 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