| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif across multiple sheets
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif across multiple sheets
See
http://www.mcgimpsey.com/excel/threedsumif.html In article , Steve wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif across multiple sheets
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif across multiple sheets
If your sheet names really are Sheet1, Sheet2, Sheet3 etc:
=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1 :9"))&"!B1"),"PMI",INDIRECT("Sheet"&ROW(INDIRECT(" 1:9"))&"!A3"))) Biff "Bob Phillips" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif across multiple sheets
The sheet names are not Sheet1, Sheet2, Sheet3 etc... I just put in for
simplicity. They can be any name. Thanks. "Biff" wrote: If your sheet names really are Sheet1, Sheet2, Sheet3 etc: =SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1 :9"))&"!B1"),"PMI",INDIRECT("Sheet"&ROW(INDIRECT(" 1:9"))&"!A3"))) Biff "Bob Phillips" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif across multiple sheets
Bob, here is what I typed in but it does not seem to work. Not sure why.
Thanks. =SUMPRODUCTS(SUMIF(INDIRECT("'"&C1:C9&"'!B1"),"PMI ",INDIRECT("'"&C1:C9&"'!A3"))) "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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif across multiple sheets
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif across multiple sheets
=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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif across multiple sheets
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif across multiple sheets
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif across multiple sheets
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif across multiple sheets
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",INDIRECT(OFFSET(LU!F1,,,COUNT A(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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif across multiple sheets
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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif across multiple sheets
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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif across multiple sheets
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 |
|
#16
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| selecting multiple sheets | Excel Worksheet Functions | |||
| Printing Multiple sheets | Excel Discussion (Misc queries) | |||
| How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions | |||
| Can I unhide multiple sheets at once? | Excel Discussion (Misc queries) | |||
| linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) | |||
Linear Mode
