Problem with Sumif formula

[Guest]

Hi
Why is this formula not working?

I want to sumif column A across a range of worksheets (hopefully if a
worksheet is added within the range of worksheets, the totals of the new
worksheet will be automatically included via this range).

=SUMIF(Sheet1:Blank5!A:A,ALL!A14,Sheet1:Blank5!D)

Also, if a worksheet name has a space in the name how does that look in the
formula: 'Sheet 1'Blank5!A:A

 

[Bob Phillips]

=SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C3&"'!A:A1000"),"value",INDIRECT("'"&C1:C3
&"'!B1:B100")))

where all the sheets are listed in C1:C3, or however many are needed.

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Biff]

Note that SUMPRODUCT doesn't work with complete

columns, you have to specify a range.

True, but in this case Sumproduct wouldn't be referencing the entire
columns, Sumif would:

=SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C3&"'!A:A"),"value",INDIRECT("'"&C1:C3&"'!B:B")))

Biff

=SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C3&"'!A:A1000"),"value",INDIRECT("'"&C1:C3
&"'!B1:B100")))

where all the sheets are listed in C1:C3, or however many are needed.

Note that SUMPRODUCT doesn't work with complete columns, you have to
specify
a range.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

I realised this straight-after posting, but thought, what the heck, the
statement is true even if it doesn't apply here

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Note that SUMPRODUCT doesn't work with complete
columns, you have to specify a range.

True, but in this case Sumproduct wouldn't be referencing the entire
columns, Sumif would:

=SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C3&"'!A:A"),"value",INDIRECT("'"&C1:C3&"'!
B:B")))

Biff

 

[Guest]

Thanks for the response Bob & Biff - but I'm getting a #REF on the formula
you suggested. I think it's because the formula is trying to include my
totals sheet which is called "All". That's where I'm using this formula to
calculate and report the totals of all the other sheets in the workbook.

I also have a DATA sheet that can't be included in the totals.
How do I exclude the ALL sheet and DATA sheet in the formula?

I didn't understand why C1:C3 and B1:B3 were nominated in your solution as
my original formula only looked at column A and column D?

So what I'm needing it to specifically look at is column A1:A300 on all
sheets (except DATA and ALL) where the word "SEARCH" appears. If SEARCH is
found I need the equivalent figures in D1300 to be summed.

This is the formula I've put in at the moment:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A300&"'!A:A"),"Search",INDIRECT("'"&A1:A300&"'!d:d")))

Sorry for my ignorance, I'm a novice at these formulas and am learning as I
go...

 

[Bob Phillips]

You are misunderstanding what I said. I did explain where C1:C3 came into
it, that was where I stored the list of sheet names. B1:B100 was just an
example of what to sum, which you should have been able to translate to your
data if you understood SUMIF.

What you need to do is store the names of the sheets that you want to sum in
column A for instance, let's say you have 4, so you enter them in A1:A4
(note, you wouldn't add ALL or DATA here). Then the formula is

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A4&"'!A1:A300"),"Search",INDIRECT("'"&A1:A
4&"'!D1300")))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)