SUMIF across multiple worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am collating a summary stock movement which using the sumif function i.e
SUMIF Sheet1!b$1:b$500,a5,a$1:a$500)

Is there a way to shorten the formula as I have presently 40 sheets for one
week which is causing me problems space wise (and hurts the eyes when looking
at the formula) as the a5 is a rolling cell and there are 945 varying cells
in the summary worsheet.
 
I am collating a summary stock movement which using the sumif function i.e
SUMIF Sheet1!b$1:b$500,a5,a$1:a$500)

Is there a way to shorten the formula as I have presently 40 sheets for one
week which is causing me problems space wise (and hurts the eyes when looking
at the formula) as the a5 is a rolling cell and there are 945 varying cells
in the summary worsheet.

It's not immediately clear what you're trying to achieve. The Sum If
formula seems about as short as it could be and can't be shortened
much more other than taking out the $s.

Are you trying to sum data for one week ( and hence 40 sheets)

What do you mean by A5 being a rolling cell?

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
I am collating a summary stock movement which using the sumif function i.e
SUMIF Sheet1!b$1:b$500,a5,a$1:a$500)

Is there a way to shorten the formula as I have presently 40 sheets for one
week which is causing me problems space wise (and hurts the eyes when looking
at the formula) as the a5 is a rolling cell and there are 945 varying cells
in the summary worsheet.

It's not immediately clear what you're trying to achieve. The Sum If
formula seems about as short as it could be and can't be shortened
much more other than taking out the $s.

Are you trying to sum data for one week ( and hence 40 sheets)

What do you mean by A5 being a rolling cell?

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
Yes I'm trying to sum multiple sheets that can contain similar information.

By a rolling cell - Product abc is a5, product def is a6 etc ...I hope this
clarifies it.

So I've been using SUMIF
(Sheet1!b$1:b$500,a5,a$1:a$500)+sumif(sheet2!b$1:b$500,a5,sheet2!a$1:a$500).....etc
 
Yes I'm trying to sum multiple sheets that can contain similar information.

By a rolling cell - Product abc is a5, product def is a6 etc ...I hope this
clarifies it.

So I've been using SUMIF
(Sheet1!b$1:b$500,a5,a$1:a$500)+sumif(sheet2!b$1:b$500,a5,sheet2!a$1:a$500).....etc
 
Why not have the sumif at the top of each sheet, then just have the
summary sheet sum across the sheet range

e.g.

Sum(Sheet1:Sheet40!A5)

Rgds


Yes I'm trying to sum multiple sheets that can contain similar information.

By a rolling cell - Product abc is a5, product def is a6 etc ...I hope this
clarifies it.

So I've been using SUMIF
(Sheet1!b$1:b$500,a5,a$1:a$500)+sumif(sheet2!b$1:b$500,a5,sheet2!a$1:a$500).....etc

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
Why not have the sumif at the top of each sheet, then just have the
summary sheet sum across the sheet range

e.g.

Sum(Sheet1:Sheet40!A5)

Rgds


Yes I'm trying to sum multiple sheets that can contain similar information.

By a rolling cell - Product abc is a5, product def is a6 etc ...I hope this
clarifies it.

So I've been using SUMIF
(Sheet1!b$1:b$500,a5,a$1:a$500)+sumif(sheet2!b$1:b$500,a5,sheet2!a$1:a$500).....etc

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
The 'master' stock list is on the summary page and I am attempting to pick up
each seperate sheet which contains any of the master items then give me a
total amount across all the sheets. To get by I have done the
=sumif(Sheet1!b$1:b$500,a5,a$1:a$500)+sumif(sheet2!b$1:b$500,a5,sheet2!a$1:a$500).....etc
but for the 40 sheets the formula is too long to fit in. If you can advise
on this it would make things much easier.

Cheers
 
If the sheets are actually named Sheet1, Sheet2, Sheet3, etc, try...

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:40"))&"!B1:B500"),A5,I
NDIRECT("Sheet"&ROW(INDIRECT("1:40"))&"!A1:A500")))

If the sheets are named differently, list the sheet names in a range of
cells, let's say D5:D44, then try...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$D$5:$D$44&"'!B1:B500"),A5,INDIRECT("'"&$D
$5:$D$44&"'!A1:A500")))

If you download and install the free add-in Morefunc.xll, you can use...

=SUMPRODUCT(--(THREED('Sheet1:Sheet40'!$B$1:$B$500)=A5),THREED('Sheet1:Sh
eet40'!$A$1:$A$500))

The add-in can be downloaded at the following link...

http://xcell05.free.fr/

Hope this helps!
 
I have tried this and it works fine on the individual sheets but I get a
#NAME report on the summary sheet!
 
I have tried this and it works fine on the individual sheets but I get a
#NAME report on the summary sheet!
 
Range("A5").Select
Range("A4:D1594").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"A1:A2"), CopyToRange:=Range("F4:I28"), Unique:=False

If I include LastRow = Cells(Rows.Count,"A").End(xlUp).Row it gives the same
result as before. What am I doing wrong please?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top