Sum function across worksheets

  • Thread starter Thread starter David
  • Start date Start date
D

David

Greetings -

I did a search and didn't find this one -- I have a
workbook with named pages, and common but not identical
data between them. I created a summary page with a
consolidated list of the common values, and need a
function to find the matching value on each page and sum
the corresponding number in the adjacent cell.

For example, each page is a monthly list of products with
a number of sales, and not all months have the same
products -- so I need to function to match the product
from the summary page and sum the sales for the months
where that product is listed.

I'm going nuts on this and feel like it's at my
fingertips -- any help you can provide would be greatly
appreciated.

Thanks,

David
 
Hi

I was able to do a similar thing by writing a "For Each"
macro in visual basics, which basically searches for the
records, copys them into a table elsewhere, sums the
numbers in that new table and then links them to the
final destination. If you've never written a macro
before, i can try to help you with that.

good luck
kari
 
Kari -

Nope haven't written an excel macro before although I have
a programming background -- any pointers you can provide
would be appreciated!

Thanks

David
 
I did a search and didn't find this one -- I have a
workbook with named pages, and common but not identical
data between them. I created a summary page with a
consolidated list of the common values, and need a
function to find the matching value on each page and sum
the corresponding number in the adjacent cell.

For example, each page is a monthly list of products with
a number of sales, and not all months have the same
products -- so I need to function to match the product
from the summary page and sum the sales for the months
where that product is listed.
Click to expand...
...

More details would help. It appears you're looking for a 3D SUMIF. There's a way
to do this using only built-in functions. The one catch is that you also need to
use a list of the worksheet names to process. Since these would be months, I'll
assume the worksheets are named for the months, e.g., January, February, March,
etc. I'll assume product IDs are found in col A in A2:A1000 (where 1000 is well
below the last actual entry) and corresponding sales figures are found col B in
B2:B1000. Further, I'll assume the sums begin with the January sheet and go
through the month specified by the defined name MonthEntry, that the products in
the summary are also listed in col A beginning in A2, and the corresponding
total sales figures would be entered in col B.

Given all these assumptions, you could use the formula

Summary!B2:
=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT("1:"&
MONTH(MonthEntry&"-1")))&"-1","mmmm")&"'!A2:A1000"),A2,
INDIRECT("'"&TEXT(ROW(INDIRECT("1:"&MONTH(MonthEntry&"-1")))
&"-1","mmmm")&"'!B2:B1000")))

Then select B2 and fill down as far as needed.
 
hi!

saw that haraln grove replied as well. his suggestion
may well work and be less complicated (might try it
myself). But here's what i did anyway.

Open the visual basics toolbar and click on the little
triangle. type your macro name and click create button.
Type your command information between the sub Name and
End sub.

This is the command i used (everything on one line until
i put a space between lines--which you shouldn't do when
you actually try this):

Set myRangekari = range("A1")
For Each c In Worksheets("January").Range("A1:A1000")

If c.Value = "item1" Then c.Range("a1").Copy
(myRangeKari.Rows(myRangekar.CurrentRegion.Rows.Count +
1))

Next

What you need to do is assign a permant range for each
item that you want to total. Then assign that range a
name (i.e.myRangekari). If you look back at the above
command, you'll see that i told excel the address of the
upper left cell of my range.

Then you need to tell excel where to begin looking for
the items that you specify. (Look in each cell in
worksheet january, cells a1 to a1000.

The rest of the command in english is as follows: If the
cell you (excel) are looking at now contains the
characters item1, then copy that cell (range "a1" here
means the top left cell of the current selection, not the
top left cell of the worksheet) to the first blank row of
myRangekari.

Be sure to leave a space after the word copy.

Then in the appropriate cell on your sum worksheet, total
the appropriate cells in myRangekari

Note: you can tile visual basics and your workbook, so
that you can see both at the same time. Then if you push
F8, you can watch each step of your macro happen.

i hope this helps some. i am new to visual basics
myself, and bought a book that i found helpful
called "Excel 2002 visual Basic For Applications Step by
Step" It's by Reed Jacobson--you might want to check it
out.

Also, i don't know your spreadsheet situation, but it
occurs to me that it might be easier to have all the
information on one worksheet in one table, say with the
months across the top and the products down the side.
Then in the far right column you could do a yearly total
for each product. And if you wanted to print a report of
just one months, you could hide all the columns that you
don't want to see and print whats left.

good luck

kari

---Original Message-----
 
Back
Top