+ Reply to Thread
Results 1 to 11 of 11

SUMIF the month with a cell reference

  1. #1
    Registered User
    Join Date
    03-26-2019
    Location
    usa
    MS-Off Ver
    2003
    Posts
    25

    SUMIF the month with a cell reference

    I want to sumif "store purchases" for Jan. in excel 2003 could someone please show me how to do this

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: SUMIF the month with a cell reference

    Hi

    an example: numbers in B1:B10, dates (real dates) in A1:A10.

    To sum numbers related to january 2019



    =SUMPRODUCT((MONTH(A1:A10)=1)*(YEAR(A1:A10)=2019)*B1:B10)

    If there is no need to filter the year

    =SUMPRODUCT((MONTH(A1:A10)=1)*B1:B10)


    Hope it could be of some help.
    Last edited by canapone; 04-03-2019 at 09:34 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    03-26-2019
    Location
    usa
    MS-Off Ver
    2003
    Posts
    25

    Re: SUMIF the month with a cell reference

    ill try it now thanks ill let you know if it works for me

  4. #4
    Registered User
    Join Date
    03-26-2019
    Location
    usa
    MS-Off Ver
    2003
    Posts
    25

    Re: SUMIF the month with a cell reference

    Here is a copy of my file. Go to the summary worksheet and in cell B13, I want a formula to sum the CATEGORIES with each MONTH as listed in the headings. Thanks
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-26-2019
    Location
    usa
    MS-Off Ver
    2003
    Posts
    25

    Re: SUMIF the month with a cell reference

    I use the 2017,2018,2019 worksheets for the corresponding table on the summarry worksheet

  6. #6
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: SUMIF the month with a cell reference

    Hi,

    a quick example before getting stuck in my daily florentine traffic jam.

    In B53 to be copied across

    =sumproduct(('2019'!$B$3:$B$100=$A53)*(month('2019'!$A$3:$A$100)=column(A$1))*'2019'!$D$3:$D$100)

    The segment

    =column(A$1)

    if copied to the right produces 1-2-...12 the numbers represent - of course- months passing by...


    Please forgive my bad English

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,385

    Re: SUMIF the month with a cell reference

    Change the headings in row 52. For example, instead of January, type 01/01/2019 and then set the formatting to custom > mmmm.

    Now you can use the MONTH function to create the date criterion for your formula.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Registered User
    Join Date
    03-26-2019
    Location
    usa
    MS-Off Ver
    2003
    Posts
    25

    Re: SUMIF the month with a cell reference

    Wow this works great Canapone copied down and over thank you so much for your help

  9. #9
    Registered User
    Join Date
    03-26-2019
    Location
    usa
    MS-Off Ver
    2003
    Posts
    25

    Re: SUMIF the month with a cell reference

    I will also try changing the row heading like you suggested AliGW

  10. #10
    Registered User
    Join Date
    03-26-2019
    Location
    usa
    MS-Off Ver
    2003
    Posts
    25

    Re: SUMIF the month with a cell reference

    Thank you both so much for helping me with this problem I really appreciate it

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,385

    Re: SUMIF the month with a cell reference

    No problem!

+ 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] sumif less than a cell reference
    By Sasquatch2014 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-16-2018, 02:34 PM
  2. Sumif same cell on sheets based on month
    By earthandbody in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-31-2017, 02:15 PM
  3. [SOLVED] Help with SUMIF using a cell as a reference
    By UK_Richie in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-16-2017, 06:18 PM
  4. [SOLVED] Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-13-2014, 09:25 PM
  5. [SOLVED] display cell to show only Month/year to do sumif calc if today()= any day of month
    By fireguy7 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-12-2013, 06:55 PM
  6. Using Cell Reference in SUMIF
    By tklaty in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-13-2007, 10:47 PM
  7. [SOLVED] I want to use sumif( > a cell reference) and not a value. How?
    By Corie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-05-2006, 03:25 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