CODEWITHSUNDEEP
excel
Karnivaurus
Karnivaurus

Reputation: 24121

Formula over multiple sheets with condition

I have five sheets named Sheet1, Sheet2, ..., Sheet5. I want to sum up the values from cell A2 to cell A10 over all these sheets. But I only want to include a sheet in this sum if its value in cell A1 is equal to 3. What is the formula I require to achieve this?

Upvotes: 2

Views: 159

Answers (2)

Bob Okafor
Bob Okafor

Reputation: 63

This code should be the end all be all on your question.

It checks each sheet and adds all the numbers (from A2 to A10) from the sheets inwhich A1 = 3 and excludes the sheets that does not meet the requirement.

I tested it.

=SUM(IF(sheet1!A1=3, SUM(sheet1!A2:A10), 0),IF(sheet2!A1=3, SUM(sheet2!A2:A10), 0),IF(sheet3!A1=3, SUM(sheet3!A2:A10), 0),IF(sheet4!A1=3, SUM(sheet4!A2:A10), 0),IF(sheet1!A5=3, SUM(sheet5!A2:A10), 0))

Upvotes: 2

XOR LX
XOR LX

Reputation: 7752

Try this:

=SUM(IF(N(INDIRECT("'Sheet"&{1,2,3,4,5}&"'!A1"))=3,SUMIF(INDIRECT("'Sheet"&{1,2,3,4,5}&"'!A2:A10"),">0")))

Regards

Upvotes: 0

Related Questions