sum of positive numbers From different sheets?

[1Ionut1]

Hello, can you help me with a formula for sum of positive numbers From different sheets? I find =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetlist&"'!F23"),">0"), but i don't know how to define "Sheetlist". An Excel sample IT will be awesome..

 

[Eric W]

Welcome to the MrExcel forum!

Here's a sample:

Book2
	A	B	C	D	E	F
1	Sheets
2	Sheet1		12
3	Sheet2
4	Sheet3
5
23						4
Sheet3

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!F23"),">0"))

Named Ranges
Name	Refers To	Cells
SheetList	=Sheet3!$A$2:$A$4	C2

To define the SheetList Name, there are a few ways to do it. The easiest is to select the range with the sheet names in it (A2:A4 in this example), then in the range descriptor above it (which should now show A2), overtype the A2 with SheetList. You can also go to the Formulas tab, click on Name Manager, then New..., then enter the name and Refers to: box.

You can also just use the range in your formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!F23"),">0"))

but a Name is more descriptive and makes the formula easier to read.