|
Help with Sumif and INDIRECT
Hello,
Im using the formula below to sum multiple sheets that has a code in cell D1. I would like to modify if possible to accommodate one more criteria in cell F1 of the summary sheet. The references for E:E and B:B should remain the same. I obtained this formula awhile back using this forum. Thank you. =SUMIF(INDIRECT("'"&A2&"'!E:E"),summary!$D$1,INDIR ECT("'"&A2&"'!B:B")) |
Help with Sumif and INDIRECT
You can only use SUMIF for a single criteria. If your criteria is that
D1 AND F1 have to be met then you will need to use SUMPRODUCT. Depending on what your criteria for F1 is, though, an alternative might be that you can concatenate two columns into a helper column and then you could still use SUMIF on this helper column - faster than SP. Give us a few more details, and I'll be able to advise further. Hope this helps. Pete On Jan 16, 3:52*pm, JHL wrote: Hello, I’m using the formula below to sum multiple sheets that has a code in cell D1. *I would like to modify if possible to accommodate one more criteria in cell F1 of the summary sheet. *The references for E:E and B:B should remain the same. I obtained this formula awhile back using this forum. * Thank you. =SUMIF(INDIRECT("'"&A2&"'!E:E"),summary!$D$1,INDIR ECT("'"&A2&"'!B:B")) |
Help with Sumif and INDIRECT
Pete thanks,
I'm not sure what you mean by helper column. Currently "D" in the multiple sheets has words like 'pending', 'released', 'deleted'. My additional column would be a date field. mm/dd/yy. I guess can use SumProd, we can't get a helper, but I like the speed of what I currently have. "Pete_UK" wrote: You can only use SUMIF for a single criteria. If your criteria is that D1 AND F1 have to be met then you will need to use SUMPRODUCT. Depending on what your criteria for F1 is, though, an alternative might be that you can concatenate two columns into a helper column and then you could still use SUMIF on this helper column - faster than SP. Give us a few more details, and I'll be able to advise further. Hope this helps. Pete On Jan 16, 3:52 pm, JHL wrote: Hello, Im using the formula below to sum multiple sheets that has a code in cell D1. I would like to modify if possible to accommodate one more criteria in cell F1 of the summary sheet. The references for E:E and B:B should remain the same. I obtained this formula awhile back using this forum. Thank you. =SUMIF(INDIRECT("'"&A2&"'!E:E"),summary!$D$1,INDIR ECT("'"&A2&"'!B:B")) |
Help with Sumif and INDIRECT
So presumably you enter a word like "pending" in D1 on the summary
sheet, and you now want to enter a date in F1, and you want the sum of column B (from the sheet stored in A2) where column E = summary!D1 and column ? = summary!F1 ? You can do this with SP, but you need to realise that you can't have full-column references with SP (unless you are using XL 2007). This is what it would look like: =SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E1000")=$D$1)*( INDIRECT("'"&A2&"'! G1:G1000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B1000")) where I have assumed that your dates are in column G on those other sheets, and that you may have up to 1000 rows in your data sheets. I've also assumed that the formula is in the summary sheet, so you don't need to specify the sheet name in front of D1, F1. What I meant by using a helper column is that you could have a formula like: =E1&G1 in (say) Z1 of your data sheets, copied down as far as you need. Then you could have: =SUMIF(INDIRECT("'"&A2&"'!Z:Z"),$D$1&$F$1,INDIRECT ("'"&A2&"'!B:B")) which will operate as quickly as your current formula. The SP version may be significantly slower, depending on how many rows you operate it over. Both should give the same results, however. Hope this helps. Pete On Jan 16, 4:54*pm, JHL wrote: Pete thanks, I'm not sure what you mean by helper column. *Currently "D" in the multiple sheets has words like 'pending', 'released', 'deleted'. *My additional column would be a date field. *mm/dd/yy. I guess can use SumProd, we can't get a helper, but I like the speed of what I currently have. "Pete_UK" wrote: You can only use SUMIF for a single criteria. If your criteria is that D1 AND F1 have to be met then you will need to use SUMPRODUCT. Depending on what your criteria for F1 is, though, an alternative might be that you can concatenate two columns into a helper column and then you could still use SUMIF on this helper column - faster than SP. Give us a few more details, and I'll be able to advise further. Hope this helps. Pete On Jan 16, 3:52 pm, JHL wrote: Hello, I’m using the formula below to sum multiple sheets that has a code in cell D1. *I would like to modify if possible to accommodate one more criteria in cell F1 of the summary sheet. *The references for E:E and B:B should remain the same. I obtained this formula awhile back using this forum. * Thank you. =SUMIF(INDIRECT("'"&A2&"'!E:E"),summary!$D$1,INDIR ECT("'"&A2&"'!B:B"))- Hide quoted text - - Show quoted text - |
Help with Sumif and INDIRECT
A helper cell would be where you have an AND test for the two criteria in
the INDIRECTed sheet, and you test for that column being TRUE instead of directly for the values. -- __________________________________ HTH Bob "JHL" wrote in message ... Pete thanks, I'm not sure what you mean by helper column. Currently "D" in the multiple sheets has words like 'pending', 'released', 'deleted'. My additional column would be a date field. mm/dd/yy. I guess can use SumProd, we can't get a helper, but I like the speed of what I currently have. "Pete_UK" wrote: You can only use SUMIF for a single criteria. If your criteria is that D1 AND F1 have to be met then you will need to use SUMPRODUCT. Depending on what your criteria for F1 is, though, an alternative might be that you can concatenate two columns into a helper column and then you could still use SUMIF on this helper column - faster than SP. Give us a few more details, and I'll be able to advise further. Hope this helps. Pete On Jan 16, 3:52 pm, JHL wrote: Hello, I'm using the formula below to sum multiple sheets that has a code in cell D1. I would like to modify if possible to accommodate one more criteria in cell F1 of the summary sheet. The references for E:E and B:B should remain the same. I obtained this formula awhile back using this forum. Thank you. =SUMIF(INDIRECT("'"&A2&"'!E:E"),summary!$D$1,INDIR ECT("'"&A2&"'!B:B")) |
Help with Sumif and INDIRECT
I've just spotted that I missed a bracket from the end of the SP
formula - should be this: =SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E1000")=$D$1)*( INDIRECT("'"&A2&"'! G1:G1000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B1000"))) Hope this helps. Pete On Jan 16, 5:30*pm, Pete_UK wrote: So presumably you enter a word like "pending" in D1 on the summary sheet, and you now want to enter a date in F1, and you want the sum of column B (from the sheet stored in A2) where column E = summary!D1 and column ? = summary!F1 ? You can do this with SP, but you need to realise that you can't have full-column references with SP (unless you are using XL 2007). This is what it would look like: =SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E1000")=$D$1)*( INDIRECT("'"&A2&"'! G1:G1000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B1000")) where I have assumed that your dates are in column G on those other sheets, and that you may have up to 1000 rows in your data sheets. I've also assumed that the formula is in the summary sheet, so you don't need to specify the sheet name in front of D1, F1. What I meant by using a helper column is that you could have a formula like: =E1&G1 in (say) Z1 of your data sheets, copied down as far as you need. Then you could have: =SUMIF(INDIRECT("'"&A2&"'!Z:Z"),$D$1&$F$1,INDIRECT ("'"&A2&"'!B:B")) which will operate as quickly as your current formula. The SP version may be significantly slower, depending on how many rows you operate it over. Both should give the same results, however. Hope this helps. Pete |
Help with Sumif and INDIRECT
I pasted your formula, but I'm getting an #Value! error. I did change the
cell range to 2000 rows where you indicated. but it's not summing. Would the format of the date cell maybe cause this? Where I have the dates, both are formatted like nn-Mon-yy copied formula: =SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E2000")=$D$1)*( INDIRECT("'"&A2&"'! G1:G2000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B2000"))) I also tried the $D$1&$F$1, and it yielded $0.00. no error, but no calculation. I verified there should be a total. "Pete_UK" wrote: I've just spotted that I missed a bracket from the end of the SP formula - should be this: =SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E1000")=$D$1)*( INDIRECT("'"&A2&"'! G1:G1000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B1000"))) Hope this helps. Pete On Jan 16, 5:30 pm, Pete_UK wrote: So presumably you enter a word like "pending" in D1 on the summary sheet, and you now want to enter a date in F1, and you want the sum of column B (from the sheet stored in A2) where column E = summary!D1 and column ? = summary!F1 ? You can do this with SP, but you need to realise that you can't have full-column references with SP (unless you are using XL 2007). This is what it would look like: =SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E1000")=$D$1)*( INDIRECT("'"&A2&"'! G1:G1000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B1000")) where I have assumed that your dates are in column G on those other sheets, and that you may have up to 1000 rows in your data sheets. I've also assumed that the formula is in the summary sheet, so you don't need to specify the sheet name in front of D1, F1. What I meant by using a helper column is that you could have a formula like: =E1&G1 in (say) Z1 of your data sheets, copied down as far as you need. Then you could have: =SUMIF(INDIRECT("'"&A2&"'!Z:Z"),$D$1&$F$1,INDIRECT ("'"&A2&"'!B:B")) which will operate as quickly as your current formula. The SP version may be significantly slower, depending on how many rows you operate it over. Both should give the same results, however. Hope this helps. Pete |
Help with Sumif and INDIRECT
Bob, thanks, if the date field would not be consecutive, would that work?
and would I have to enter pending, and various dates until the formula worked? "Bob Phillips" wrote: A helper cell would be where you have an AND test for the two criteria in the INDIRECTed sheet, and you test for that column being TRUE instead of directly for the values. -- __________________________________ HTH Bob "JHL" wrote in message ... Pete thanks, I'm not sure what you mean by helper column. Currently "D" in the multiple sheets has words like 'pending', 'released', 'deleted'. My additional column would be a date field. mm/dd/yy. I guess can use SumProd, we can't get a helper, but I like the speed of what I currently have. "Pete_UK" wrote: You can only use SUMIF for a single criteria. If your criteria is that D1 AND F1 have to be met then you will need to use SUMPRODUCT. Depending on what your criteria for F1 is, though, an alternative might be that you can concatenate two columns into a helper column and then you could still use SUMIF on this helper column - faster than SP. Give us a few more details, and I'll be able to advise further. Hope this helps. Pete On Jan 16, 3:52 pm, JHL wrote: Hello, I'm using the formula below to sum multiple sheets that has a code in cell D1. I would like to modify if possible to accommodate one more criteria in cell F1 of the summary sheet. The references for E:E and B:B should remain the same. I obtained this formula awhile back using this forum. Thank you. =SUMIF(INDIRECT("'"&A2&"'!E:E"),summary!$D$1,INDIR ECT("'"&A2&"'!B:B")) |
Help with Sumif and INDIRECT
So I was right in supposing that the dates were in column G, or did
you forget to change that to your own column? I assumed that you were working with proper date values, and so the formatting does not affect things. However, if one (eg F1) is a text value and the other is a true date, then these will not match. One way of testing is to format the cell(s) as General, and if they are true dates then you should see the cell(s) display a number slightly less than 40,000. If the display does not change, however, then it is a text value. Hope this helps. Pete On Jan 16, 7:01*pm, JHL wrote: I pasted your formula, but I'm getting an #Value! error. *I did change the cell range to 2000 rows where you indicated. *but it's not summing. *Would the format of the date cell maybe cause this? *Where I have the dates, both are formatted like nn-Mon-yy copied formula: =SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E2000")=$D$1)*( INDIRECT("'"&A2&"'! G1:G2000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B2000"))) I also tried the $D$1&$F$1, and it yielded $0.00. *no error, but no calculation. *I verified there should be a total. "Pete_UK" wrote: I've just spotted that I missed a bracket from the end of the SP formula - should be this: =SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E1000")=$D$1)*( INDIRECT("'"&A2&"'! G1:G1000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B1000"))) Hope this helps. Pete On Jan 16, 5:30 pm, Pete_UK wrote: So presumably you enter a word like "pending" in D1 on the summary sheet, and you now want to enter a date in F1, and you want the sum of column B (from the sheet stored in A2) where column E = summary!D1 and column ? = summary!F1 ? You can do this with SP, but you need to realise that you can't have full-column references with SP (unless you are using XL 2007). This is what it would look like: =SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E1000")=$D$1)*( INDIRECT("'"&A2&"'! G1:G1000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B1000")) where I have assumed that your dates are in column G on those other sheets, and that you may have up to 1000 rows in your data sheets. I've also assumed that the formula is in the summary sheet, so you don't need to specify the sheet name in front of D1, F1. What I meant by using a helper column is that you could have a formula like: =E1&G1 in (say) Z1 of your data sheets, copied down as far as you need. Then you could have: =SUMIF(INDIRECT("'"&A2&"'!Z:Z"),$D$1&$F$1,INDIRECT ("'"&A2&"'!B:B")) which will operate as quickly as your current formula. The SP version may be significantly slower, depending on how many rows you operate it over. Both should give the same results, however. Hope this helps. Pete- Hide quoted text - - Show quoted text - |
Help with Sumif and INDIRECT
Pete, the dates in the individual spreadsheets are in column G
The date fields are formatted correctly. Still have #Value! error "Pete_UK" wrote: So I was right in supposing that the dates were in column G, or did you forget to change that to your own column? I assumed that you were working with proper date values, and so the formatting does not affect things. However, if one (eg F1) is a text value and the other is a true date, then these will not match. One way of testing is to format the cell(s) as General, and if they are true dates then you should see the cell(s) display a number slightly less than 40,000. If the display does not change, however, then it is a text value. Hope this helps. Pete On Jan 16, 7:01 pm, JHL wrote: I pasted your formula, but I'm getting an #Value! error. I did change the cell range to 2000 rows where you indicated. but it's not summing. Would the format of the date cell maybe cause this? Where I have the dates, both are formatted like nn-Mon-yy copied formula: =SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E2000")=$D$1)*( INDIRECT("'"&A2&"'! G1:G2000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B2000"))) I also tried the $D$1&$F$1, and it yielded $0.00. no error, but no calculation. I verified there should be a total. "Pete_UK" wrote: I've just spotted that I missed a bracket from the end of the SP formula - should be this: =SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E1000")=$D$1)*( INDIRECT("'"&A2&"'! G1:G1000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B1000"))) Hope this helps. Pete On Jan 16, 5:30 pm, Pete_UK wrote: So presumably you enter a word like "pending" in D1 on the summary sheet, and you now want to enter a date in F1, and you want the sum of column B (from the sheet stored in A2) where column E = summary!D1 and column ? = summary!F1 ? You can do this with SP, but you need to realise that you can't have full-column references with SP (unless you are using XL 2007). This is what it would look like: =SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E1000")=$D$1)*( INDIRECT("'"&A2&"'! G1:G1000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B1000")) where I have assumed that your dates are in column G on those other sheets, and that you may have up to 1000 rows in your data sheets. I've also assumed that the formula is in the summary sheet, so you don't need to specify the sheet name in front of D1, F1. What I meant by using a helper column is that you could have a formula like: =E1&G1 in (say) Z1 of your data sheets, copied down as far as you need. Then you could have: =SUMIF(INDIRECT("'"&A2&"'!Z:Z"),$D$1&$F$1,INDIRECT ("'"&A2&"'!B:B")) which will operate as quickly as your current formula. The SP version may be significantly slower, depending on how many rows you operate it over. Both should give the same results, however. Hope this helps. Pete- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 11:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com