Summing total per customer for each month

[wnstar21]

Please see the attached example.

I am working with Office 97 and I know that if I had Office 2007 this question could be easily answered with the "SUMIFS" function. But since Office 97 doesn't have that formula I need to find another way to accomplish what i am trying to do.

In the gray boxes I need a formula that will sum the amounts for each customer for each month. For example, the total amount that shipped to customer A in October is 180.4.

I will be applying this to a much larger amount of data, so please do not provide a formula that only works for the data supplied in this example. I will be continuously adding more info to my list over time and I want the count to automaticaly include the data when I enter it.

Thank you!

[darkyam]

Perhaps the attached will work.

[wnstar21]

It doesn't seem to be fully working, as for Customer A in October it should be 180.4 and November is 49.6.

Custoemr B looks fine, but customer C should have 16.1 in October.

Can you explain a little in words what the formula is doing?

Thank you

[darkyam]

Sorry about that. Didn't have the named ranges set right. Each named range starts off at row 6 of the column it's for. Index is used in the named range to create a reference that takes the row from cell 6 through however many records you have. In other words, for the named range Amount (=Sheet1!$A$6:INDEX(Sheet1!$A:$A,COUNT(Sheet1!$A:$A)+5)), it starts at A6, counts how many records there are (22) and then adds 5 since we skipped those five rows, making the reference A6:A27.

The formula in the sheet itself compares the dates in ShipDate to the dates in column F and, if the cell below the row you're working on is 0, simply multiplies the results by 1 instead of checking to see whether there are dates after a given date. This is important because, if you had December dates in your table, but no 12/1/09 in your summary table, November would return those December records. The formula then checks the Customer list for the customer in Row 5 and, if the results of all three checks are TRUE (or 1), it adds up the corresponding amount in the Amount list.

[wnstar21]

OK I see how it is done. But instead of naming ranges, can I just tell the formula in the calculating cell a specific range (ie. C47:C2500, 2500 just a big number) or put a whole column in as the range, even if the column has a header and the info to be counted doesn't start until row 132?

[darkyam]

You can't do whole columns, simply because it will try to do math on the headers and return a #VALUE! error. You can just put in large ranges if you want, starting with row 6. Just remember to confirm with Ctrl+Shift+Enter.

[wnstar21]

OK thanks. One last question. I havent used Crtl+Shift+Enter before. What does it do?

[darkyam]

It is used for array formulas. I honestly don't understand why the Microsoft developers couldn't have made the formulas work without them, but suffice it to say that for formulas such as this one (or others that have conditions or use functions meant for one cell on a range), you need to confirm it this way.

[wnstar21]

OK, I get it. Thanks again!

[c_m_s_jr]

Would a pivot table not work using the group by function?

[c_m_s_jr]

See attached for an example using a pivot table

[Jbentley]

I'd also suggest perusing the various threads on SUMPRODUCT.
Using this function allowys for very distinct criteria

[darkyam]

I actually tried that first, but for some reason, it wouldn't work when I tried to have it multiply by an array if there was a date for the next month in the summary table and by 1 if there wasn't.

[DonkeyOte]

In SUMPRODUCT terms (using the named ranges already configured)

Please Login or Register  to view this content.

For info. on Sumproduct & Arrays see links in sig.
(SUMPRODUCT is in essence MS's Array function that does not require CSE to commit, ie it is processed like an array (an iteration of sorts))

c_m_s_jr's point re: Pivots is however perhaps the most valid.