sumif (I think)

[jlkirk]

I have 11 worksheets. Worksheets 1-10 are named by year, i. e., "2000", "2001","2002" through "2009". Worksheet 11 is a summary. On each of the "yearly" worksheets, cells A2:A20 contains a list of warehouse locations (some may vary from year to year-that is, some may be present in one year and not the next, etc.). Cells E2:P20 set out the inventory levels at each location at the end of each month of that particular year

In the summary worksheet, I have set up cells F1:O1 by year (i.e., F1="2000", G1="2001", etc.). Cells A2:A20 are all of the warehouse locations. What I would like to do in cells F2:O24 is total the inventory for a given year for a given location. For example, in F2, I would like to have a formula that sums the total of inventory for the warehouse in A2 for the year 2000 (the date in F1 that corresponds to the first "yearly" worksheet).

Any ideas?

Thaks in advance.

[HansV]

Enter the following formula in cell F2 on the summary sheet:

=SUM(INDIRECT("'"&F$1&"'!$E"&ROW()&":$P"&ROW()))

Fill down to F24, then right to column O.

[jlkirk]

Thanks Hans for the quick reply. Unfortunately, it is giving me a "ref message. Would you mind breaking down the formula so that I might see where I have screwed up?
Thanks.

[HansV]

In cell F2, F1 contains 2000, and ROW() equals 2.
So "'"&F$1&"'!$"&ROW()&":$P"&ROW() evaluates to the string '2000'!$E2:$P2.
INDIRECT converts this to a cell reference and SUM calculates its sum.

[jlkirk]

Nope. As I said, some of the warehouse locations appear in one year and not in another. I think we need to also look at the warehouse name each year as well as the year. In other words, if "Main" warehouse appears at A5 in year 2002 and A7 in year 2008, the summary is not picking up the correct numbers.

[HansV]

Try this then:

=SUMPRODUCT((INDIRECT("'"&F$1&"'!$A$2:$A$24")=$A2)*INDIRECT("'"&F$1&"'!$E$2:$P$24"))

[BobSullivan]

Wouldn't the consolidate function work here? If all the column labels are the same, the consolidate command will list each warehouse on its own row, and show the inventory totals combined where they should be combined, and separate where they should be unique.

[HansV]

Thanks for the suggestion. I don't think consolidation would create a separate column for each year (sheet), but Jeff can decide for himself.

[jlkirk]

Thanks Hans for your revised formula; it works just fine.

[jlkirk]

I was just handed a further twist, part of which I can handle but the other part I will need some help with. Assume the following revised facts: On a new sheet 1 (the name of which is the year, say "2000", "2001", etc.), in A1 is the warehouse name. In B2:M2, the months of the year in format xx/xx/xxxx. In A4:A34 are the days of the month (numerical). In B4:M34 are the inventory levels for each day of each month.

On sheet 2, in A2:A13, I have the months of the year that are on sheet 1 in cells B2:M2. In B2:B13 on sheet 2, I will enter a formula to compute the avarage inventory levels for each month. In C2:C13, I will enter a formula to compute the maximum inventory levels for each month, and in E2:E13, I will do the same for the minimum. What I need help on is in D2:D13, I would like to enter the day of the month that the maximum occurs, and in F2:F13, the day of the month the minimum occurs.

Any help?

Thanks in advance.

[HansV]

Could you post a small sample workbook (with the fomulas for min and max)?

[jlkirk]

The max formula would simply be "=max(2000!B4:2000!B34)" in C2 of sheet 2 to compute the max for the month of January of the year 2000, and "=min(2000!B4:2000!B34)" in E2 of sheet 2 to compute the min for the month of January of the year 2000, and fill across accordingly for the other eleven months.

[HansV]

Something like =INDEX('2000'!$A$4:$A$34,MATCH(C2,'2000'!B$4:B$34,0))

[jlkirk]

thanks Hans