Need help summing sheets based on a variable

  • Thread starter Thread starter Leatherfoot
  • Start date Start date
L

Leatherfoot

I have 20 sheets (named Vendor1 to Vendor20) that have data in cell
B:20 to M38 on each sheet.

Also on each sheet are three text fields called Region, Type, an
Volume (B10,B11 and B12) that can have a few different variables.

What I need to be able to do is sum up the 20 sheets based on the tex
variables. For example, I may want to sum up the sheets that hav
"eastern" in the B10 text field or sheets that have "eastern" in B1
AND "high" in B12.

If i could get just one variable to work I could figure out the rest.

I tried a sumif but it doesn't seem to like going 3d into sheet
(#VALUE error). I'm a novice at this so I'm a bit stumped.

Any help would be appreciated
 
hi,i'm in China,why don't you try to add a column tagged "Vendor Name
or something to your data,so you can list all the necessary data i
just one sheet without blundering among different sheets,and after tha
,according to what you mentioned in your posting, i guess you may wan
to sum up the volume data according to 2 or even more conditions,i
that' s the case,there's at least two different ways to do it,1 yo
could use auto filter plus Subtotal fuction ,2,you could use an arra
formula.does that ring a bell? you could send me a email a
(e-mail address removed) if you have any questions.glad of be hel
 
The data would not fit into a single column for each vendor. the dat
has Date periods and each date row has about 12 product types so i
would be impossible to put in one column.

I want to use sheets since I want to add vendors on the fly as this i
not a fixed amount and by summing sheet Vendor1 to the last sheet aptl
named "end" I can add vendors in between without mucking with all th
formulas.

Here's a pic of the data on the sheet (the text fields are off screen)

[image: http://www.bingo1.ca/bot.jpg
 
Well I tried a SUMIF but get a #value error. I guess it doesn't lik
spanning sheets.

HELP
 
One workaround:

Put your SUMIF() on each sheet (conditional summing for that sheet
only), in an out-of-the-way location, say, cell Z1. Perhaps something
like:

=IF(AND(B10="eastern",B12="high"),SUM(B20:M38),0)

Then on your summary sheet, use

=SUM('Sheet1:Sheet20'!Z1)
 
Back
Top