software

I have the following formula that I am trying to use but the result is #VALUE:

=SUMIF(Sheet1:Sheet9!B1,quot;PMIquot;,Sheet1:Sheet9!A3)

The formula should look in cell B1 of each sheet and if it is equal to quot;PMIquot;
sum of the value in cell A3 in each sheet.

What am I doing wrong or is this possible?

Thanks

See

www.mcgimpsey.com/excel/threedsumif.html

In article gt;,
Steve gt; wrote:

gt; I have the following formula that I am trying to use but the result is #VALUE:
gt;
gt; =SUMIF(Sheet1:Sheet9!B1,quot;PMIquot;,Sheet1:Sheet9!A3)
gt;
gt; The formula should look in cell B1 of each sheet and if it is equal to quot;PMIquot;
gt; sum of the value in cell A3 in each sheet.
gt;
gt; What am I doing wrong or is this possible?
gt;
gt; Thanks

Try putting the sheet names in C1:c9 and use

=SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!B1quot;),quot;PMIquot; ,INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!A3quot;
)))--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Stevequot; gt; wrote in message
...
gt; I have the following formula that I am trying to use but the result is
#VALUE:
gt;
gt; =SUMIF(Sheet1:Sheet9!B1,quot;PMIquot;,Sheet1:Sheet9!A3)
gt;
gt; The formula should look in cell B1 of each sheet and if it is equal to
quot;PMIquot;
gt; sum of the value in cell A3 in each sheet.
gt;
gt; What am I doing wrong or is this possible?
gt;
gt; Thanks
If your sheet names really are Sheet1, Sheet2, Sheet3 etc:

=SUMPRODUCT(SUMIF(INDIRECT(quot;Sheetquot;amp;ROW(INDIRECT(quot;1 :9quot;))amp;quot;!B1quot;),quot;PMIquot;,INDIRECT(quot;Sheetquot;amp;ROW(INDIRECT(quot; 1:9quot;))amp;quot;!A3quot;)))

Biff

quot;Bob Phillipsquot; gt; wrote in message
...
gt; Try putting the sheet names in C1:c9 and use
gt;
gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!B1quot;),quot;PMIquot; ,INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!A3quot;
gt; )))
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Stevequot; gt; wrote in message
gt; ...
gt;gt; I have the following formula that I am trying to use but the result is
gt; #VALUE:
gt;gt;
gt;gt; =SUMIF(Sheet1:Sheet9!B1,quot;PMIquot;,Sheet1:Sheet9!A3)
gt;gt;
gt;gt; The formula should look in cell B1 of each sheet and if it is equal to
gt; quot;PMIquot;
gt;gt; sum of the value in cell A3 in each sheet.
gt;gt;
gt;gt; What am I doing wrong or is this possible?
gt;gt;
gt;gt; Thanks
gt;
gt;
The sheet names are not Sheet1, Sheet2, Sheet3 etc... I just put in for
simplicity. They can be any name.

Thanks.

quot;Biffquot; wrote:

gt; If your sheet names really are Sheet1, Sheet2, Sheet3 etc:
gt;
gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;Sheetquot;amp;ROW(INDIRECT(quot;1 :9quot;))amp;quot;!B1quot;),quot;PMIquot;,INDIRECT(quot;Sheetquot;amp;ROW(INDIRECT(quot; 1:9quot;))amp;quot;!A3quot;)))
gt;
gt; Biff
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt; gt; Try putting the sheet names in C1:c9 and use
gt; gt;
gt; gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!B1quot;),quot;PMIquot; ,INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!A3quot;
gt; gt; )))
gt; gt;
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;Stevequot; gt; wrote in message
gt; gt; ...
gt; gt;gt; I have the following formula that I am trying to use but the result is
gt; gt; #VALUE:
gt; gt;gt;
gt; gt;gt; =SUMIF(Sheet1:Sheet9!B1,quot;PMIquot;,Sheet1:Sheet9!A3)
gt; gt;gt;
gt; gt;gt; The formula should look in cell B1 of each sheet and if it is equal to
gt; gt; quot;PMIquot;
gt; gt;gt; sum of the value in cell A3 in each sheet.
gt; gt;gt;
gt; gt;gt; What am I doing wrong or is this possible?
gt; gt;gt;
gt; gt;gt; Thanks
gt; gt;
gt; gt;
gt;
gt;
gt;

Bob, here is what I typed in but it does not seem to work. Not sure why.
Thanks.

=SUMPRODUCTS(SUMIF(INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!B1quot;),quot;PMI quot;,INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!A3quot;)))quot;Bob Phillipsquot; wrote:

gt; Try putting the sheet names in C1:c9 and use
gt;
gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!B1quot;),quot;PMIquot; ,INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!A3quot;
gt; )))
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Stevequot; gt; wrote in message
gt; ...
gt; gt; I have the following formula that I am trying to use but the result is
gt; #VALUE:
gt; gt;
gt; gt; =SUMIF(Sheet1:Sheet9!B1,quot;PMIquot;,Sheet1:Sheet9!A3)
gt; gt;
gt; gt; The formula should look in cell B1 of each sheet and if it is equal to
gt; quot;PMIquot;
gt; gt; sum of the value in cell A3 in each sheet.
gt; gt;
gt; gt; What am I doing wrong or is this possible?
gt; gt;
gt; gt; Thanks
gt;
gt;
gt;

Bob, I figured out what my problem was. Thanks.

I have one more question. If the range C1:C9 is on another sheet, how would
I type this in?

Thanks.quot;Bob Phillipsquot; wrote:

gt; Try putting the sheet names in C1:c9 and use
gt;
gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!B1quot;),quot;PMIquot; ,INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!A3quot;
gt; )))
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Stevequot; gt; wrote in message
gt; ...
gt; gt; I have the following formula that I am trying to use but the result is
gt; #VALUE:
gt; gt;
gt; gt; =SUMIF(Sheet1:Sheet9!B1,quot;PMIquot;,Sheet1:Sheet9!A3)
gt; gt;
gt; gt; The formula should look in cell B1 of each sheet and if it is equal to
gt; quot;PMIquot;
gt; gt; sum of the value in cell A3 in each sheet.
gt; gt;
gt; gt; What am I doing wrong or is this possible?
gt; gt;
gt; gt; Thanks
gt;
gt;
gt;

=SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;'other
sheet'!C1:C9amp;quot;'!B1quot;),quot;PMIquot;,INDIRECT(quot;'quot;amp;'other sheet'!C1:C9amp;quot;'!A3quot;)))--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Stevequot; gt; wrote in message
news
gt; Bob, I figured out what my problem was. Thanks.
gt;
gt; I have one more question. If the range C1:C9 is on another sheet, how
would
gt; I type this in?
gt;
gt; Thanks.
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Try putting the sheet names in C1:c9 and use
gt; gt;
gt; gt;
=SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!B1quot;),quot;PMIquot; ,INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!A3quot;
gt; gt; )))
gt; gt;
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;Stevequot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I have the following formula that I am trying to use but the result is
gt; gt; #VALUE:
gt; gt; gt;
gt; gt; gt; =SUMIF(Sheet1:Sheet9!B1,quot;PMIquot;,Sheet1:Sheet9!A3)
gt; gt; gt;
gt; gt; gt; The formula should look in cell B1 of each sheet and if it is equal to
gt; gt; quot;PMIquot;
gt; gt; gt; sum of the value in cell A3 in each sheet.
gt; gt; gt;
gt; gt; gt; What am I doing wrong or is this possible?
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt;
gt; gt;
gt; gt;
Wrap around wasn't good for that

=SUMPRODUCT(SUMIF(
INDIRECT(quot;'quot;amp;'other sheet'!C1:C9amp;quot;'!B1quot;),quot;PMIquot;,
INDIRECT(quot;'quot;amp;'other sheet'!C1:C9amp;quot;'!A3quot;)))--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Bob Phillipsquot; gt; wrote in message
...
gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;'other
gt; sheet'!C1:C9amp;quot;'!B1quot;),quot;PMIquot;,INDIRECT(quot;'quot;amp;'other sheet'!C1:C9amp;quot;'!A3quot;)))
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Stevequot; gt; wrote in message
gt; news
gt; gt; Bob, I figured out what my problem was. Thanks.
gt; gt;
gt; gt; I have one more question. If the range C1:C9 is on another sheet, how
gt; would
gt; gt; I type this in?
gt; gt;
gt; gt; Thanks.
gt; gt;
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; Try putting the sheet names in C1:c9 and use
gt; gt; gt;
gt; gt; gt;
gt;
=SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!B1quot;),quot;PMIquot; ,INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!A3quot;
gt; gt; gt; )))
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Stevequot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; I have the following formula that I am trying to use but the result
is
gt; gt; gt; #VALUE:
gt; gt; gt; gt;
gt; gt; gt; gt; =SUMIF(Sheet1:Sheet9!B1,quot;PMIquot;,Sheet1:Sheet9!A3)
gt; gt; gt; gt;
gt; gt; gt; gt; The formula should look in cell B1 of each sheet and if it is equal
to
gt; gt; gt; quot;PMIquot;
gt; gt; gt; gt; sum of the value in cell A3 in each sheet.
gt; gt; gt; gt;
gt; gt; gt; gt; What am I doing wrong or is this possible?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
Bob, I decided to open the range to use to the last cell with something in it
using the COUNTA function but this doesn't seem to work. Thanks.

=SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;LU!F1:INDIRECT(quot;Fquot;amp; COUNTA(LU!F1:F1500))amp;quot;'!B1quot;),$A$1,INDIRECT(quot;'quot;amp;LU! F1:INDIRECT(quot;Fquot;amp;COUNTA(LU!F1:F1500))amp;quot;'!A3quot;)))quot;Bob Phillipsquot; wrote:

gt; Wrap around wasn't good for that
gt;
gt; =SUMPRODUCT(SUMIF(
gt; INDIRECT(quot;'quot;amp;'other sheet'!C1:C9amp;quot;'!B1quot;),quot;PMIquot;,
gt; INDIRECT(quot;'quot;amp;'other sheet'!C1:C9amp;quot;'!A3quot;)))
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt; gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;'other
gt; gt; sheet'!C1:C9amp;quot;'!B1quot;),quot;PMIquot;,INDIRECT(quot;'quot;amp;'other sheet'!C1:C9amp;quot;'!A3quot;)))
gt; gt;
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;Stevequot; gt; wrote in message
gt; gt; news
gt; gt; gt; Bob, I figured out what my problem was. Thanks.
gt; gt; gt;
gt; gt; gt; I have one more question. If the range C1:C9 is on another sheet, how
gt; gt; would
gt; gt; gt; I type this in?
gt; gt; gt;
gt; gt; gt; Thanks.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Try putting the sheet names in C1:c9 and use
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!B1quot;),quot;PMIquot; ,INDIRECT(quot;'quot;amp;C1:C9amp;quot;'!A3quot;
gt; gt; gt; gt; )))
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Stevequot; gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; I have the following formula that I am trying to use but the result
gt; is
gt; gt; gt; gt; #VALUE:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =SUMIF(Sheet1:Sheet9!B1,quot;PMIquot;,Sheet1:Sheet9!A3)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; The formula should look in cell B1 of each sheet and if it is equal
gt; to
gt; gt; gt; gt; quot;PMIquot;
gt; gt; gt; gt; gt; sum of the value in cell A3 in each sheet.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; What am I doing wrong or is this possible?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt;