Help needed using the serial number date with sumifs - whole office is stumped

[FraserKitchell]

Hi all,

I've got a question thats been stumping all of us around the office for a few days now and it seems simple enough but we just can't get at the formula. Basically, I want to create a formula that looks something like this:

=SUMIF($A$12:$A$200, ">=B2", $B$12:$B$200)-SUMIF($A$12:$A$200, ">C2", $B$12:$B$200)



Where B2 and C2 are both dates driven off of a cell. The problem is, I cant this darn sumif to understand that these are dates unless I hard type them into the formula for each cell. I have tried changing the formatting of the cells, tried them as text, and as a serial number date, etc, but nothing seems to work. From what I can gather from this forum, I am beginning to wonder if Sumif is even the correct formula or if a sumproduct or something would be better.

I've attached a spreadsheet with some real data in it if you'd like to take a look.

Thanks! My office eagerly awaits the answer

[mcjohn]

I believe this will work for you. I had fun wasting time at work trying to solve this lol. There's also ways to filter out specific data if say you had an additional column for types of hdd or something and wanted to know how many of those between a given range.

[mcjohn]

I'm sure this is obvious, but here's the file showing how the formula can be applied to different date ranges. I prefer personally to use named ranges for large data as it manages it easier and is clearer to people when looking at the formula what is is being done. As an example I named the range of dates (to dates) from the first date all the way down randomly to the 1372 row and same for the hdd (named hdd). So any new dates and hdd values put in below your set provided will be within the range. You could name it for hundredes of thousands of cells if you want. Do you understand naming ranges? Let me know.

[FraserKitchell]

Right on! I dabbled in the SUMIFS but couldnt get them to work like you did. I appreciate the time spent at work figuring this out! gotta love getting paid to practice your excel skillz.