Sum Cells Based on Month

[jkelly228]

Hello All,

On the attached spreadsheet you have an "Item List", "Order Type", "Quantities" and "Dates" on Sheet 2. I need a program that will look at the item list, Idenfity the Order Type and Month, then Put the Quantity value in the proper Item Type, Order Type, Month slot. If there is more than one then it needs to add them together.

This will be for hundreds of part numbers, but the attached spreadsheet just uses 2 examples. I am toying with the idea of doing a pivot, but I wanted to see if it is possible to keep the data in the original format and do an automatic Sum.

I have several different spreadsheets that I can use this type of VB for, so any help is greatly appreciated.

[Cutter]

There is a formula solution.

Put this formula in cell E5 and copy throughout:

Please Login or Register  to view this content.

[alansidman]

I created a pivot table that closely resembles your desired output.

Alan

[nimrod]

excel 2007 - 2010 has a formula for this called "SumIfs". It allows you to sum numbers based on matching multiple criteria in multiple columns. Before 2007 people would accomplish the same using Sumproduct with a double unary minus method ... but that method is much more CPU intensive so it's recommended that the built-in SumIf's be utilized.

[Cutter]

@nimrod

perhaps you could show us how that formula would look

[nimrod]

Hi Cutter: I'd be happy to ... here's the formula for J5 of Sheet1...

Criteria
Item Number = ABC
Family = 1234
Order Type = On Hand
Date range : Month of .... oopss did April instead of June ... but you get the idea

Please Login or Register  to view this content.

Please see uploaded sheet

[Cutter]

You aren't suggesting the OP write an individual formula for each cell are you?

How about offering a more user friendly version.

[nimrod]

LOL .. actually this formula , like all others can be tweaked with absolute versus relatives cell references etc. For Example A5 can Become A$5 , B$5 , Anc C$5 .. but you kwow this ... right ? The point being the SumIFs formula was introduced in 2007 because so many people were requesting Multi-Criteria SumIf's ... and that's what this situation was tailored made for .

Cheers

[Cutter]

OK, nimrod, I absolutely agree that SUMIFS() is a MUCH better choice than is SUMPRODUCT(). I am definitely NOT a fan of SUMPRODUCT() because I have seen firsthand how power hungry it is.

The OP didn't specifically ask for a formula so I offered it to let him/her know there was a formula option.

So here is your formula adjusted for dragging purposes.

The dates in row 1 will have to be first of month dates and can be formatted as mmm-yy.

In E5 and copied throughout:

=SUMIFS(Sheet2!$D:$D,Sheet2!$A:$A,$A5,Sheet2!$B:$B,$B5,Sheet2!$C:$C,$C5,Sheet2!$E:$E,">="&E$1,Sheet2!$E:$E,"<"&EDATE(E$1,1))

[nimrod]

HI Cutter:
Love the improvements on my example... and the OP should appreciate it too.
Cheers

[Cutter]

Team Canada!

[nimrod]

Yeah Team ! .. Cheers for now , look forward to exchanging ideas in the future.

[jkelly228]

Thank you all for the suggestions. I really appreciate it.

So far though, I am leaning towards the use of a Pivot Table first and then filling in blanks. Here is my issue:

Say for Item Number "DEF" there is no "Planned Demand" (just for arguments sake), then that row isn't going to show up, but I still need it represented as Zero's in the final format. So taking a direct pivot and copying and pasting into the final format will only go so far.

So then my program would need to look at the "Order Type" field for every "Item Number" and make sure that every item number has all order types. Does this make sense?

I have been working on just directly looking at the Item Number, Family and Order Type and copying to the first sheet. Below is the code that I have so far (taken from some help Watersev gave me on another problem). Still tweaking it a lot, but just showing what I am doing so far.

Please Login or Register  to view this content.

[jkelly228]

Hello All,

Please take a look at Spreadsheet 6 on the attached file. I have automated the Pivot table and the seperation of Qty's by date. Pretty easy stuff I know.

The next step is that I need to then get the Item Number, Family, and Order type synced with the Qty by month on Sheet 1. While I know this simple spreadsheet makes it seem like I could just copy and paste it in, but the real spreadsheet isn't that easy.

I need to systematically get a crossreference of Item, Family and Order Type with the correct Qty in the correct month.

I appreciate any help that you can provide.

Kelly

[jkelly228]

Hello all,

OK. So I am thinking of taking a different direction. I haven't really had a response that fits what I need exactely, though I do appreciate those that have taken the time to help,

In this new direction, I am just taking the Item Number and Order Type on Sheet 3 and comparing it to the Item Number and Order Type. When a match is found then copy the row from column "D" through "K".

What I need then is to move to Sheet 1 and paste it to column "E" through "L". Below is the code that I have so far. Any help is greatly appreciated.

Please Login or Register  to view this content.

[jkelly228]

I got it to switch sheets, now just find the right Item Number and Order Type in Sheet1 so I can paste the information from Sheet3. Below is the updated Code

Please Login or Register  to view this content.