Posted to microsoft.public.excel.worksheet.functions
|
|
sumif across multiple sheets
Bob, I may have forgotten to tell you that the formula should be on a
seperate tab, not the LU tab. I was able to get it to work the way you did
also but not if the formula is on a seperate tab. Sorry for the confusion.
Thanks, Steve
"Bob Phillips" wrote:
Steve,
I have posted a working example at
http://www.xldynamic.com/example%20c...e%20sheets.xls
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Steve" wrote in message
...
I am getting a #REF! error using the formulas I attached below.
Thanks, Steve
"Bob Phillips" wrote:
Works great for me Steve, what are you seeing?
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Steve" wrote in message
...
Bob, this is what I tried and it does not work. By the way "LU" is a
tab
name that I am using to lookup a name.
Thanks, Steve
=SUMPRODUCT(SUMIF(INDIRECT(OFFSET(LU!F1,,,COUNTA(L U!F:F))&"!B1"),"PMI",INDIR
ECT(OFFSET(LU!F1,,,COUNTA(LU!F:F))&"!A3")))
"Bob Phillips" wrote:
You know what they say, give a mouse some cheese, and he wants some
milk
<vbg
=SUMPRODUCT(SUMIF(
INDIRECT(OFFSET(F1,,,COUNTA(F:F))&"!B1"),"PMI",
INDIRECT(OFFSET(F1,,,COUNTA(F:F))&"!A3")))
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Steve" wrote in message
...
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("'"&LU!F1:INDIRECT("F"& COUNTA(LU!F1:F1500))&"'!B1
"),$A$1,INDIRECT("'"&LU!F1:INDIRECT("F"&COUNTA(LU! F1:F1500))&"'!A3")))
"Bob Phillips" wrote:
Wrap around wasn't good for that
=SUMPRODUCT(SUMIF(
INDIRECT("'"&'other sheet'!C1:C9&"'!B1"),"PMI",
INDIRECT("'"&'other sheet'!C1:C9&"'!A3")))
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Bob Phillips" wrote in
message
...
=SUMPRODUCT(SUMIF(INDIRECT("'"&'other
sheet'!C1:C9&"'!B1"),"PMI",INDIRECT("'"&'other
sheet'!C1:C9&"'!A3")))
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Steve" wrote in message
...
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.
"Bob Phillips" wrote:
Try putting the sheet names in C1:c9 and use
=SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C9&"'!B1"),"PMI" ,INDIRECT("'"&C1:C9&"'!A3"
)))
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Steve" wrote in message
...
I have the following formula that I am trying to use but
the
result
is
#VALUE:
=SUMIF(Sheet1:Sheet9!B1,"PMI",Sheet1:Sheet9!A3)
The formula should look in cell B1 of each sheet and if
it
is
equal
to
"PMI"
sum of the value in cell A3 in each sheet.
What am I doing wrong or is this possible?
Thanks
|