+ Reply to Thread
Results 1 to 19 of 19

IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

  1. #1
    Registered User
    Join Date
    09-21-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Hi everyone, I'm trying to prepare my data for a pivot table. The context / purpose is that I'm trying to determine the rate of how much hard drive space my company purchases over time for each of our datacenters. My spreadsheet has 3 columns:

    Column1 - Quote Dates: has random dates from 2010 to present
    Column2 - Datacenter: has any of 5 or 6 datacenter names where the hard drive space was ordered for
    Column3 - Terabytes: has the number of Terabytes purchased on that date.

    I have added a new column named "Dates" which has all first days of the month since 1/1/2010. I want to have a SUM of purchased storage for each month so that when I add my pivot table/chart, I have all months present for data points.

    I have tried a lot of things and it seems like text or Month(cellrange) do not work well with IF, AND, IF(AND(, or SUMIF/SUMIFS. Would anyone ever take a look at my spreadsheet (B2 contains my latest formula attempt) and let me know if it's possible to do what I want?

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Try...
    In B2
    =SUMPRODUCT(--($K$2:$K$207=B$1),--(MONTH($I$2:$I$207)=MONTH($A2)),--(YEAR($I$2:$I$207)=YEAR($A2)),$J$2:$J$207)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    08-24-2012
    Location
    Schiedam, holland
    MS-Off Ver
    Excel 2003-2013
    Posts
    38

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Storage Purchases 2010 and upTest.xlsx

    I think is better to use pivot chart. Check if the attached file meets your need.

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

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Try this formula in B2 copied across and down

    =SUMIFS($J:$J,$K:$K,B$1,$I:$I,">="&$A2,$I:$I,"<="&EOMONTH($A2,0))
    Audere est facere

  5. #5
    Registered User
    Join Date
    09-21-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Quote Originally Posted by Ace_XL View Post
    Try...
    In B2
    =SUMPRODUCT(--($K$2:$K$207=B$1),--(MONTH($I$2:$I$207)=MONTH($A2)),--(YEAR($I$2:$I$207)=YEAR($A2)),$J$2:$J$207)
    Thanks for the reply. I tried it but it ends up still just being zeros all the way down. I made sure to try Ctrl+Shift+Enter as well.

  6. #6
    Registered User
    Join Date
    09-21-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Quote Originally Posted by daddylonglegs View Post
    Try this formula in B2 copied across and down

    =SUMIFS($J:$J,$K:$K,B$1,$I:$I,">="&$A2,$I:$I,"<="&EOMONTH($A2,0))
    Thanks for this as well, but same thing - still only produces 0.

  7. #7
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Thanks for the reply. I tried it but it ends up still just being zeros all the way down.
    works for me. see attached
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-21-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Quote Originally Posted by shadrack View Post
    Attachment 182160

    I think is better to use pivot chart. Check if the attached file meets your need.
    This looks good but can you tell me where you are getting the "years" row label from? I'd like to show an entry for every month so which is what started my quest to populate the values. My understanding is that I need points from each month in order to be able to properly project time-wise into the future.

    Thank you very much for the quick response and to everyone else as well.

  9. #9
    Registered User
    Join Date
    09-21-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Quote Originally Posted by Ace_XL View Post
    works for me. see attached
    **EDITING OUT**It does look like it's working for you, except what about B6? There should be the sum of J2 and J3 in there.**EDITEDOUT***

    My mistake, you are right, and that did work for you. I was misreading the Network tier name. I just have to figure out why it wasn't working for me.
    Last edited by jrochet; 09-22-2012 at 08:53 AM.

  10. #10
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    It does look like it's working for you, except what about B6? There should be the sum of J2 and J3 in there.
    I would think that sum of J2 and J3 would be populated in C6 under "External DMZ SJ"

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

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Quote Originally Posted by jrochet View Post
    Thanks for this as well, but same thing - still only produces 0.
    See attached
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    09-21-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Quote Originally Posted by Ace_XL View Post
    I would think that sum of J2 and J3 would be populated in C6 under "External DMZ SJ"
    And you're right. I was mixing SJ and MON. Thanks very much. It is working for me too btw, I was giving up by B6 based off of my mistake in mixing those up.

  13. #13
    Registered User
    Join Date
    09-21-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Quote Originally Posted by daddylonglegs View Post
    See attached
    This works too. I was also giving up on this one too early by looking for something that would never show up in B6. Thanks to everyone, I'm impressed how fast you all pulled this off considering I've been at it for so long and had no progress.

  14. #14
    Registered User
    Join Date
    09-21-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Is there any chance Ace_XL or DaddyLongLegs that you could explain the logic in your solutions (both work) so I can try and wrap my head around it for future use? I apologize for asking but I've been all through my mom's acadamy of learning books on Excel 2010 and they just don't seem to cover this, at least in a way that I can relate to my task.

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

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    In this formula

    =SUMIFS($J:$J,$K:$K,B$1,$I:$I,">="&$A2,$I:$I,"<="&EOMONTH($A2,0))

    The range to sum is the first one, column J, and then you list, column + condition for however many conditions you need so the first condition is that column K = B$1 ($ sign before row number stops that from changing as you copy down), now for the date condition you can't use MONTH against the columns in SUMIFS so you need to check that column I is >= to $A2 (second condition) - $ against column letter to stop that changing as you copy across, and that column I is also <= to the last day of the month (third condition) - last day of the month can be obtained with EOMONTH function

  16. #16
    Registered User
    Join Date
    09-21-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Quote Originally Posted by daddylonglegs View Post
    In this formula

    =SUMIFS($J:$J,$K:$K,B$1,$I:$I,">="&$A2,$I:$I,"<="&EOMONTH($A2,0))

    The range to sum is the first one, column J, and then you list, column + condition for however many conditions you need so the first condition is that column K = B$1 ($ sign before row number stops that from changing as you copy down), now for the date condition you can't use MONTH against the columns in SUMIFS so you need to check that column I is >= to $A2 (second condition) - $ against column letter to stop that changing as you copy across, and that column I is also <= to the last day of the month (third condition) - last day of the month can be obtained with EOMONTH function
    Awesome! Thank you very much. I just googled 'SUMIFS ampersand' and figured out that SUMIFS needs it for cell references so I am fully clear on your formula.

  17. #17
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    In this formula..

    =SUMPRODUCT(--($K$2:$K$207=B$1),--(MONTH($I$2:$I$207)=MONTH($A2)),--(YEAR($I$2:$I$207)=YEAR($A2)),$J$2:$J$207)

    Entries in Column J will be summed if all conditions are met i.e. Col K=B1, Month in ColI equals Month of A2, Year in ColI equals Year of A2

  18. #18
    Registered User
    Join Date
    09-21-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Quote Originally Posted by Ace_XL View Post
    In this formula..

    =SUMPRODUCT(--($K$2:$K$207=B$1),--(MONTH($I$2:$I$207)=MONTH($A2)),--(YEAR($I$2:$I$207)=YEAR($A2)),$J$2:$J$207)

    Entries in Column J will be summed if all conditions are met i.e. Col K=B1, Month in ColI equals Month of A2, Year in ColI equals Year of A2
    Thanks again! Wasn't expecting this kind of service from a free membership here.

  19. #19
    Registered User
    Join Date
    08-24-2012
    Location
    Schiedam, holland
    MS-Off Ver
    Excel 2003-2013
    Posts
    38

    Re: IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns

    Storage Purchases 2010 and upTest.xlsx

    You can collapse or expand the years by clicking on the plus-sign.

    Please mark your thread solved if you are satisfied with the answers.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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