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.

Storage Purchases 2010 and upTest.xlsx

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

Originally Posted by Ace_XL

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.

Originally Posted by daddylonglegs

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.

Originally Posted by shadrack

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.

Originally Posted by Ace_XL

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.

Originally Posted by daddylonglegs

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.

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.

Originally Posted by daddylonglegs

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.

Originally Posted by Ace_XL

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.

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.