CODEWITHSUNDEEP
excelexcel-formula
SSMI
SSMI

Reputation: 113

SUMIFS across a range of sheets

I'm building an excel home finances/budget workbook. I would like to keep the transactions for each account on separate sheets with my budget sheet separate from those. I was looking for a way to sum the categories without hard-coding the names of the sheets used. I setup the sheets as follows.

Budget - Start - Account1 - Account2 - End

I have more accounts to add once i get it working. This is all prototyping to make sure I can do what I want.

I found I can do the following to sum everything on all sheets between Start and End (as long as those are blank).

=SUM(Start:End!B1:B10)

What I can't get working is a SUMIFS

=SUMIFS(Start:End!B1:B10,Start:End!A1:A10,"Count")

In column A a cell that has "Count" or "Don't". These will eventually become categories. The above gives me a "#VALUE" error. I wasn't able to get SUMIF to work either. I may add other conditions down the road.

Am I doing something wrong? Is there an easy way to do something like this?

Upvotes: 1

Views: 1259

Answers (1)

IntaStellar
IntaStellar

Reputation: 28

I got it to work by making a Range E2:E4 containing the names of the sheets, the first being "Start" and the last being "End".

I also have a criteria box E7 with value "Count"

Start

1

I've populated columns A1:A10 with either "Count" or "Don't". I've populated columns B1:B10 with numbers.

Sheet2(left) and End(right)

2

Then I used this formula to return the desired result:

=SUMPRODUCT(SUMIF(INDIRECT("'"&E2:E4&"'!A1:A10"),E7,INDIRECT("'"&E2:E4&"'!B1:B10")))

Don't know if that helps you but it does produce the desired results with the extra work of writing up a sheet list and a criteria list.

Upvotes: 0

Related Questions