SUMIF over multiple worksheets

  • Thread starter Thread starter B J
  • Start date Start date
B

B J

How can I use sum if over a range of cells on multiple
worksheets?

e.g., this doesn't work...

=SUMIF('Abba:Bjork'!C19,"=M",'Abba:Bjork'!D19)
 
Okay, thanks for the suggestion, but I get a #REF error
when using the IF function as shown below.

bj
 
Insert two new worksheets and name them First and Last. Place all the
relevant sheets in between First and Last.

=SUMPRODUCT(--(THREED(First:Last!$C19)="M"),THREED(First:Last!$D19))

THREED is available in the morefunc.xll add-in, which you can download from:
http://longre.free.fr/english/index.html.
 
Hi

It looks like I missed the point, so forget it.

I'm afraid SUMIF doesn't do here - likely it doesn't support non-contignous
ranges at all.
P.e. =SUMIF(A2:A100;C2:C100,"X",C2:C100) gives error message, and as
principle it's same what you tried.

Possible workaround:

Create an UDF

Public Function TabName(TabIndex As Integer) As String
Application.Volatile
TabName = Sheets(TabIndex).Name
End Function

Insert a worksheet Summary, containing table with TabName (It's header in
A1) as first column, and set it as firs tab
A2=IF(ISERROR(TABNAME(ROW(A2))),"",TABNAME(ROW(A2)))
and copy it down as much as you want/as much tou have sheets or more to have
some spare rows
Into B2 enter the formula
=IF(ISERROR(INDIRECT(A2 & "!C19")),"",INDIRECT(A2 & "!C19"))
Into C2 enter the formula
=IF(ISERROR(INDIRECT(A2 & "!D19")),"",INDIRECT(A2 & "!D19"))
Copy both formulas down too
I think from here on it's easy.
 
How can I use sum if over a range of cells on multiple
worksheets?

e.g., this doesn't work...

=SUMIF('Abba:Bjork'!C19,"=M",'Abba:Bjork'!D19)
Click to expand...

I may get a chance to beat Peo to answering this. You'll need either an array of
the worksheet names between Abba and Bjork or a range containing them. Order
isn't important. Then use INDIRECT and SUMPRODUCT in addition to SUMIF. Like so

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Abba","AC/DC","B52s","Bjork"}&"'!"&
CELL("Address",C19)),"M",INDIRECT("'"&{"Abba","AC/DC","B52s","Bjork"}&"'!"&
CELL("Address",D19))))

Note that this leaves C19 and D19 as fully relative cell addresses, so if you
copy the cell containing this formula and paste into the cell below it, the
formula in that cell would be

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Abba","AC/DC","B52s","Bjork"}&"'!"&
CELL("Address",C20)),"M",INDIRECT("'"&{"Abba","AC/DC","B52s","Bjork"}&"'!"&
CELL("Address",D20))))
 
Harlan Grove said:
...

I may get a chance to beat Peo to answering this. You'll need either an array of
the worksheet names between Abba and Bjork or a range containing them. Order
isn't important. Then use INDIRECT and SUMPRODUCT in addition to SUMIF. Like so

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Abba","AC/DC","B52s","Bjork"}&"'!"&
CELL("Address",C19)),"M",INDIRECT("'"&{"Abba","AC/DC","B52s","Bjork"}&"'!"&
CELL("Address",D19))))

Note that this leaves C19 and D19 as fully relative cell addresses, so if you
copy the cell containing this formula and paste into the cell below it, the
formula in that cell would be

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Abba","AC/DC","B52s","Bjork"}&"'!"&
CELL("Address",C20)),"M",INDIRECT("'"&{"Abba","AC/DC","B52s","Bjork"}&"'!"&
CELL("Address",D20))))
Click to expand...


LOL!

I like the amendment of CELL to the formula, I prefer that to the method I
have used before
using row or column to make in this case C20 relative so it can be copied
down or across.
I am getting to like the CELL function more and more, I have started to
incorporate it to some formulas
using ROW to retain the relative reference when rows are inserted above the
formula.
 
Back
Top