Excel 2007 : Sumifs w/ Data in Horizontal Arrangement & Critieria Part of Sum Range

**tdwenger** · 01-31-2011, 06:16 PM

I've got a problem that I've been trying to solve for most of the day. Let me try to lay it out for you.

If have my data arranged horizontally for ease of entry and I would like to sum certain cells if certain conditions are met.

DATA ARRANGEMENT
Column B: Customer Name
Column C: PO#
Column D: PO Date
Column E: Type
Column F: Total Quantity
Column G: Ship Date
Column H: Quantity to Ship
Column I: Ship Date
Column J: Quantity to Ship
Column K: Ship Date
Column L: Quantity to Ship
Column M: Ship Date
Column N: Quantity to Ship
Column O: Ship Date
Column P: Quantity to Ship
Column Q: Ship Date
Column R: Quantity to Ship

Columns G through R reflect the fact that multiple shipments are often made on one purchase order.

I would like to sum the values in columns H, J, L, N, P, R based on whether the Customer Name and Type in the row match the same values on another worksheet and based on whether the date to the left of Quantity falls within a given month.

The data on my reporting spreadsheet is arranged as follows:

Column A: Customer Name
Column D: Quantity (where the sum would be reported)
Cell A2: Type

I would like to end up with the quantity to be shipped in a given month for a given customer and type.

The thing that seems to be giving me the biggest problem is that one of the criteria is referring to a value that to the left of the numbers that I want to sum and therefore part of the Sum Range.

Please help!!!

Thanks!

**Richard Buttrey** · 01-31-2011, 06:24 PM

Hi,

Upload your workbook or at least a representative sample of it, along with examples of the results you expect to see and why, and no doubt someone will take a look. It's always easier to see the request in its context.

Regards

**daddylonglegs** · 01-31-2011, 06:50 PM

Try this formula in D5

=SUMPRODUCT((Data!$B$4:$B$100=$A5)*(Data!$E$4:$E$100=$A$2)*(Data!$H$3:$R$3="Quantity")*(TEXT(Data!$G$4:$Q$100,"mmm")=C$3),Data!$H$4:$R$100)

**tdwenger** · 01-31-2011, 07:01 PM

Daddylonglegs . . . with a little tweaking to your formula I think I've got it!!! Can you explain why and how this works, so I know for the future. I played with Sumproduct for a long time and couldn't get it to work the way you did, you've saved me!!!

THANK YOU!!!!!

**tdwenger** · 12-15-2011, 07:48 PM

When I did this earlier this year I was shortsighted and didn't take into account the year of the Purchase Order. Now that we are nearing the end of the year, the tool is just cycling back to January of 2011 because the year isn't involved in the formula. Is there a way that someone might be able to modify the formula to take the year into account?

Thanks!

**Richard Buttrey** · 12-15-2011, 08:13 PM

Hi,

Try

Please Login or Register  to view this content.

change the first element as necessary to pick up the required year.

**daddylonglegs** · 12-15-2011, 08:29 PM

The original assumed that C3 would contain the 3 letter text month, e.g. Nov, assuming that this cell contains a date in that month (preferably 1st but it doesn't matter), e.g. 1-Nov-2011 then you can amend to

=SUMPRODUCT((Data!$B$4:$B$100=$A5)*(Data!$E$4:$E$100=$A$2)*(Data!$H$3:$R$3="Quantity")*( TEXT(Data!$G$4:$Q$100,"mmm-yy")=TEXT(C$3,"mmm-yy"),Data!$H$4:$R$100)