sumif accross multiple worksheets

R

Ricky Morris

Active Member
Joined
Mar 31, 2002
Messages
363
Sumif doesn't seems to work accross multiple sheets. How do I accomplish the same results either with an array formula or vba. Excel doesn't seems to recognize a range accross multiple sheets as a valid reference: =ISREF(Sheet2:Sheet3!B10) returns a false. I get the same result when I try to pass the following into a custom function:

Cell A1 = mysum(Sheet2:Sheet3!B10), where mysum is:

Public Function mysum(v As Variant)
Application.Volatile
test = WorksheetFunction.Sum(v)
End Function
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Ricky Morris said:
Sumif doesn't seems to work accross multiple sheets. How do I accomplish the same results either with an array formula or vba. Excel doesn't seems to recognize a range accross multiple sheets as a valid reference: =ISREF(Sheet2:Sheet3!B10) returns a false. I get the same result when I try to pass the following into a custom function:

Cell A1 = mysum(Sheet2:Sheet3!B10), where mysum is:

Public Function mysum(v As Variant)
Application.Volatile
test = WorksheetFunction.Sum(v)
End Function
Click to expand...

Ricky,

Long time no see! :LOL:

Using Morefunc add-in

=SUMPRODUCT((THREED(First:Last!$A$1:$A$100)=B1)+0,(THREED(First:Last!$D$1:$D$100)))

Where A1:A100 houses the criteria B1 and D1:D100 the coulmn to sum
 
Upvote 0
Ricky,

Neglected to mention you need to insert two new worksheets and name them first and last and insert all relevant worksheets between first and last.

For examples do a search for Morefunc and Threed with Aladin as the author.
 
Upvote 0
I take a look for the add-in. I need this to work on machines that might not have the add-in since it will be used by others. Do you know if the vb code of the add-in function is protected. I would prefer a solution that resides in the workbook.
 
Upvote 0
Ricky Morris said:
...doesn't seems to work accross multiple sheets. How do I accomplish the same results either with an array formula or vba. Excel doesn't seems to recognize a range accross multiple sheets as a valid reference: =ISREF(Sheet2:Sheet3!B10) returns a false. I get the same result when I try to pass the following into a custom function:

Cell A1 = mysum(Sheet2:Sheet3!B10), where mysum is:
....
Click to expand...
Hi Ricky:

In reference to using the SUM function across multiple sheets in VBA -- you can try ...
Code: 
[a1]="=sum(sheet2:sheet3!B10)"
[a1].value=[a1].value
I hope this helps. If I have misunderstood your question -- my appologies!
 
Upvote 0
Yogi, what I'm looking for is a the SUMIF function to work accross mutiple sheets, or a coding of it's equivalent.
 
Upvote 0
Hi Ricky Morris:

Elaborating on what Paddy has referenced to, in your case, use the following formulation ...

=SUM(SUMIF(INDIRECT("Sheet"&ROW(2:3)&"!B10"),Value to be matched in cell B10))

This is an array based formula and must be entered with CTRL+SHIFT+ENTER rather than with just ENTER.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
SUMIFS between dates where dataset has multiple entries
Replies
2
Views
209
Fluff
Fluff
O
  • Question
Dividing a sumifs() statement by another sumifs() statement - Problem: Second sumifs() not recognizing valid ranges.
Replies
2
Views
271
jkpieterse
jkpieterse
O
  • Question
sumif or sumproduct with multiple sheets & rows
2
Replies
11
Views
1K
oldman33
O
J
  • Question
using sumifs function across multiple sheets
Replies
4
Views
268
KRice
K
L
  • Question
VBA Worksheet_Change event for multiple worksheets
Replies
0
Views
316
Lil Stinker
L

Forum statistics

Threads
1,215,430
Messages
6,124,849
Members
449,194
Latest member
HellScout

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top