software

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

--
Thank for your help
BeSmart

=SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C3amp;quot;'!A:A1000quot;), quot;valuequot;,INDIRECT(quot;'quot;amp;C1:C3
amp;quot;'!B1:B100quot;)))

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)

quot;BeSmartquot; gt; wrote in message
...
gt; Hi
gt; Why is this formula not working?
gt;
gt; I want to sumif column A across a range of worksheets (hopefully if a
gt; worksheet is added within the range of worksheets, the totals of the new
gt; worksheet will be automatically included via this range).
gt;
gt; =SUMIF(Sheet1:Blank5!A:A,ALL!A14,Sheet1:Blank5!D )
gt;
gt; Also, if a worksheet name has a space in the name how does that look in
the
gt; formula: 'Sheet 1'Blank5!A:A
gt;
gt; --
gt; Thank for your help
gt; BeSmart
gt;Note that SUMPRODUCT doesn't work with complete
gt;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(quot;'quot;amp;C1:C3amp;quot;'!A:Aquot;),quot;val uequot;,INDIRECT(quot;'quot;amp;C1:C3amp;quot;'!B:Bquot;)))

Biff

quot;Bob Phillipsquot; gt; wrote in message
...
gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C3amp;quot;'!A:A1000quot;), quot;valuequot;,INDIRECT(quot;'quot;amp;C1:C3
gt; amp;quot;'!B1:B100quot;)))
gt;
gt; where all the sheets are listed in C1:C3, or however many are needed.
gt;
gt; Note that SUMPRODUCT doesn't work with complete columns, you have to
gt; specify
gt; a range.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;BeSmartquot; gt; wrote in message
gt; ...
gt;gt; Hi
gt;gt; Why is this formula not working?
gt;gt;
gt;gt; I want to sumif column A across a range of worksheets (hopefully if a
gt;gt; worksheet is added within the range of worksheets, the totals of the new
gt;gt; worksheet will be automatically included via this range).
gt;gt;
gt;gt; =SUMIF(Sheet1:Blank5!A:A,ALL!A14,Sheet1:Blank5!D )
gt;gt;
gt;gt; Also, if a worksheet name has a space in the name how does that look in
gt; the
gt;gt; formula: 'Sheet 1'Blank5!A:A
gt;gt;
gt;gt; --
gt;gt; Thank for your help
gt;gt; BeSmart
gt;
gt;
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)

quot;Biffquot; gt; wrote in message
...
gt; gt;Note that SUMPRODUCT doesn't work with complete
gt; gt;columns, you have to specify a range.
gt;
gt; True, but in this case Sumproduct wouldn't be referencing the entire
gt; columns, Sumif would:
gt;
gt;
=SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C3amp;quot;'!A:Aquot;),quot;val uequot;,INDIRECT(quot;'quot;amp;C1:C3amp;quot;'!
B:Bquot;)))
gt;
gt; Biff
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt; gt;
=SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C3amp;quot;'!A:A1000quot;), quot;valuequot;,INDIRECT(quot;'quot;amp;C1:C3
gt; gt; amp;quot;'!B1:B100quot;)))
gt; gt;
gt; gt; where all the sheets are listed in C1:C3, or however many are needed.
gt; gt;
gt; gt; Note that SUMPRODUCT doesn't work with complete columns, you have to
gt; gt; specify
gt; gt; a range.
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;BeSmartquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; Hi
gt; gt;gt; Why is this formula not working?
gt; gt;gt;
gt; gt;gt; I want to sumif column A across a range of worksheets (hopefully if a
gt; gt;gt; worksheet is added within the range of worksheets, the totals of the
new
gt; gt;gt; worksheet will be automatically included via this range).
gt; gt;gt;
gt; gt;gt; =SUMIF(Sheet1:Blank5!A:A,ALL!A14,Sheet1:Blank5!D )
gt; gt;gt;
gt; gt;gt; Also, if a worksheet name has a space in the name how does that look in
gt; gt; the
gt; gt;gt; formula: 'Sheet 1'Blank5!A:A
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; Thank for your help
gt; gt;gt; BeSmart
gt; gt;
gt; gt;
gt;
gt;
Thanks for the response Bob amp; 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 quot;Allquot;. 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 quot;SEARCHquot; 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(quot;'quot;amp;A1:A300amp;quot;'!A:Aquot;),quot;S earchquot;,INDIRECT(quot;'quot;amp;A1:A300amp;quot;'!d:dquot;)))

Sorry for my ignorance, I'm a novice at these formulas and am learning as I
go...
--
Thank for your help
BeSmart
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(quot;'quot;amp;A1:A4amp;quot;'!A1:A300quot;), quot;Searchquot;,INDIRECT(quot;'quot;amp;A1:A
4amp;quot;'!D1300quot;)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;BeSmartquot; gt; wrote in message
...
gt; Thanks for the response Bob amp; Biff - but I'm getting a #REF on the formula
gt; you suggested. I think it's because the formula is trying to include my
gt; totals sheet which is called quot;Allquot;. That's where I'm using this formula
to
gt; calculate and report the totals of all the other sheets in the workbook.
gt;
gt; I also have a DATA sheet that can't be included in the totals.
gt; How do I exclude the ALL sheet and DATA sheet in the formula?
gt;
gt; I didn't understand why C1:C3 and B1:B3 were nominated in your solution
as
gt; my original formula only looked at column A and column D?
gt;
gt; So what I'm needing it to specifically look at is column A1:A300 on all
gt; sheets (except DATA and ALL) where the word quot;SEARCHquot; appears. If SEARCH
is
gt; found I need the equivalent figures in D1300 to be summed.
gt;
gt; This is the formula I've put in at the moment:
gt;
gt;
=SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;A1:A300amp;quot;'!A:Aquot;),quot;S earchquot;,INDIRECT(quot;'quot;amp;A1:A30
0amp;quot;'!d:dquot;)))
gt;
gt; Sorry for my ignorance, I'm a novice at these formulas and am learning as
I
gt; go...
gt; --
gt; Thank for your help
gt; BeSmart
gt;
gt;