Sum only positive numbers across multiple sheets

[irfananeeza]

Hi, I have multiple sheets with same range. I am using below formula to sum only positive numbers but formula gives #value! error. Any help would be appreciated.

=SUMIF(Bank1:Credit3!H4:H1000,">0",Bank1:Credit3!H4:H1000)

 

[Domenic]

First, list your sheet names in a range of cells, let's say A2:A5. Then try the following formula...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$5&"'!H4:H1000"),">0"))

Hope this helps!

 

[irfananeeza]

The formula worked beautifully. Thanks for your help.

Would appreciate you clarify my confusion here ...

Q1: I have sheets Bank1, 2, 3, Credit1,2,3. Because the cell range is exactly the same for all sheets. Therefore, In my SUMIF formula I selected all sheets Bank1:Credit3!H4:H1000. Why this method did not work.? Is it because SUMIF formula won't recognize reference to Bank1:Credit3?

Q2: How Indirect function is helping SUMIF in a very layman's term. And what action Sumproduct performs when it receives SumIF results in paranthesis?

 

[Domenic]

The formula worked beautifully. Thanks for your help.

You're very welcome.

Q1: I have sheets Bank1, 2, 3, Credit1,2,3. Because the cell range is exactly the same for all sheets. Therefore, In my SUMIF formula Bank1:Credit3!H4:H1000, I selected all sheets (select Bank1, press shift select last sheet Credit3). Why this method did not work.? Is it because SUMIF formula won't recognize reference to Bank1:Credit3?

That's right.

Q2: How Indirect function is helping SUMIF in a very layman's term. And what action Sumproduct performs when it receives SumIF results in paranthesis?

The INDIRECT function returns a reference specified by a string. In our example, INDIRECT returns an array of references. So, for example, let's say that A2:A4 contains Sheet1, Sheet2, and Sheet3. That part of the formula would be evaluated as follows...

INDIRECT("'"&$A$2:$A$4&"'!H4:H1000")

INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"}&"'!H4:H1000")

INDIRECT({"'Sheet1";"'Sheet2";"'Sheet3"}&"'!H4:H1000")

INDIRECT({"'Sheet1'!H4:H1000";"'Sheet2'!H4:H1000";"'Sheet3'!H4:H1000"})

So, as you can see, INDIRECT returns an array of references, which is then passed to SUMIF as its first argument.

Hope this helps!

 

[irfananeeza]

Thanks for visual depiction of behind the scene workings. It certainly helped. Thanks again.

 

[irfananeeza]

Sorry, one more question. What message board tool did you use to present each answer in grey section and Excel formula with orange/blue combination? Thx.

 

[Domenic]

First, enter your formula.

Then select/highlight the formula.

Then click on the XLS button on the toolbar.

Then code tags will automatically be added.

Then, once you've finished, click on the Post reply button.

 

[irfananeeza]

Thanks for all the help.

 

[Domenic]

You're very welcome, cheers!