Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Criteria Syntax in SUMIF formula
I originally asked:
How do you use SUMIF, when your criteria is one of two(or multiple) values? For Example, I want to use: =SUMIF(A2:A10, ($C$1 or $D$1), B2:B10), but I know ($C$1 or $D$1) is not the correct syntax. To which I got this answer: Just add them: =SUMIF() + SUMIF() -- Gary''s Student - gsnu200810 This will not solve my dilemna. I replied to that with this, but i'm afraid the post will not be read. So I am re-asking the question. Thank you, but this is what I want to do: Here is the actual formula that I need to change: =IF(ISBLANK('29'!$AS$2:$AS$5000),"",SUMIF('29'!$AS $2:$AS$5000,$C$2,'29'!$AT$2:$AT$5000)). I want to use the Find and Replace function to replace the criteria "$C$2" with the desired expression ($C$2 or $K$1). I didn't originally design this worksheet, and I am stuck with the original logic. There are actually over 150 tabs with 31 formulas on each sheet to change. I wanted to use the Repace all function, selecting multiple sheets (tabs), so I can change all the formulas at one time. Changing it with your suggestion would mean, if I am understanding you correctly, would change the formula to : =IF(ISBLANK('29'!$AS$2:$AS$5000),"",SUMIF('29'!$AS $2:$AS$5000,$C$2,'29'!$AT$2:$AT$5000+SUMIF('29'!$A S$2:$AS$5000,$K$1,'29'!$AT$2:$AT$5000)) The Sheet tab '29" refers to a sheet with data from the 29th of the month. The next formula on the next row is: =IF(ISBLANK('30"AS$2:$AS$5000),"",SUMIF('30'S$2:$A S$5000,$C$2,'29'!$AT$2:$AT$5000)). So, I cannot fill down formulas. This is why I would like to change the formula using the Find and Replace method. I thought there could be an expression in the criteria part of the formula that can refer to one of two or more values. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Criteria Syntax in SUMIF formula
As I stated in your other post that people still read, you cannot do what you
are wanting to do. There is a 'way' that SUMIF can take multiple criteria, but it still won't work for your purposes. Check Dave's response to this post: http://www.microsoft.com/office/comm...7-197b6dc1d381 It shows you can have 2 values within the sumif, but it changes the formula to have an additional SUM around your SUMIF formula. Why are you adverse to changing the formula if it not ONLY achieves the desired result but is also easier to modify in the future? -- ** John C ** "RollieG" wrote: I originally asked: How do you use SUMIF, when your criteria is one of two(or multiple) values? For Example, I want to use: =SUMIF(A2:A10, ($C$1 or $D$1), B2:B10), but I know ($C$1 or $D$1) is not the correct syntax. To which I got this answer: Just add them: =SUMIF() + SUMIF() -- Gary''s Student - gsnu200810 This will not solve my dilemna. I replied to that with this, but i'm afraid the post will not be read. So I am re-asking the question. Thank you, but this is what I want to do: Here is the actual formula that I need to change: =IF(ISBLANK('29'!$AS$2:$AS$5000),"",SUMIF('29'!$AS $2:$AS$5000,$C$2,'29'!$AT$2:$AT$5000)). I want to use the Find and Replace function to replace the criteria "$C$2" with the desired expression ($C$2 or $K$1). I didn't originally design this worksheet, and I am stuck with the original logic. There are actually over 150 tabs with 31 formulas on each sheet to change. I wanted to use the Repace all function, selecting multiple sheets (tabs), so I can change all the formulas at one time. Changing it with your suggestion would mean, if I am understanding you correctly, would change the formula to : =IF(ISBLANK('29'!$AS$2:$AS$5000),"",SUMIF('29'!$AS $2:$AS$5000,$C$2,'29'!$AT$2:$AT$5000+SUMIF('29'!$A S$2:$AS$5000,$K$1,'29'!$AT$2:$AT$5000)) The Sheet tab '29" refers to a sheet with data from the 29th of the month. The next formula on the next row is: =IF(ISBLANK('30"AS$2:$AS$5000),"",SUMIF('30'S$2:$A S$5000,$C$2,'29'!$AT$2:$AT$5000)). So, I cannot fill down formulas. This is why I would like to change the formula using the Find and Replace method. I thought there could be an expression in the criteria part of the formula that can refer to one of two or more values. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Criteria Syntax in SUMIF formula
Because it will take a lot longer to change the formulas. Using a simple find
and replace method would not take as long, and I would not have to try to enter this formula as an array formula. Dave Peterson's suggestion is what I would want to do, but it only seems to work when I enter the actual strings, instead of the cells. "John C" wrote: As I stated in your other post that people still read, you cannot do what you are wanting to do. There is a 'way' that SUMIF can take multiple criteria, but it still won't work for your purposes. Check Dave's response to this post: http://www.microsoft.com/office/comm...7-197b6dc1d381 It shows you can have 2 values within the sumif, but it changes the formula to have an additional SUM around your SUMIF formula. Why are you adverse to changing the formula if it not ONLY achieves the desired result but is also easier to modify in the future? -- ** John C ** "RollieG" wrote: I originally asked: How do you use SUMIF, when your criteria is one of two(or multiple) values? For Example, I want to use: =SUMIF(A2:A10, ($C$1 or $D$1), B2:B10), but I know ($C$1 or $D$1) is not the correct syntax. To which I got this answer: Just add them: =SUMIF() + SUMIF() -- Gary''s Student - gsnu200810 This will not solve my dilemna. I replied to that with this, but i'm afraid the post will not be read. So I am re-asking the question. Thank you, but this is what I want to do: Here is the actual formula that I need to change: =IF(ISBLANK('29'!$AS$2:$AS$5000),"",SUMIF('29'!$AS $2:$AS$5000,$C$2,'29'!$AT$2:$AT$5000)). I want to use the Find and Replace function to replace the criteria "$C$2" with the desired expression ($C$2 or $K$1). I didn't originally design this worksheet, and I am stuck with the original logic. There are actually over 150 tabs with 31 formulas on each sheet to change. I wanted to use the Repace all function, selecting multiple sheets (tabs), so I can change all the formulas at one time. Changing it with your suggestion would mean, if I am understanding you correctly, would change the formula to : =IF(ISBLANK('29'!$AS$2:$AS$5000),"",SUMIF('29'!$AS $2:$AS$5000,$C$2,'29'!$AT$2:$AT$5000+SUMIF('29'!$A S$2:$AS$5000,$K$1,'29'!$AT$2:$AT$5000)) The Sheet tab '29" refers to a sheet with data from the 29th of the month. The next formula on the next row is: =IF(ISBLANK('30"AS$2:$AS$5000),"",SUMIF('30'S$2:$A S$5000,$C$2,'29'!$AT$2:$AT$5000)). So, I cannot fill down formulas. This is why I would like to change the formula using the Find and Replace method. I thought there could be an expression in the criteria part of the formula that can refer to one of two or more values. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Criteria Syntax in SUMIF formula
It also requires a modification of your formula, as you need an additional
SUM around your SUMIF formula. Yes, as RagDyeR indicated in your other post, these are literal strings. You could use INDIRECT in conjunction with it, however, you still run into the problem of having to modify other parts of your formula because you need to add the SUM around the SUMIF. -- ** John C ** "RollieG" wrote: Because it will take a lot longer to change the formulas. Using a simple find and replace method would not take as long, and I would not have to try to enter this formula as an array formula. Dave Peterson's suggestion is what I would want to do, but it only seems to work when I enter the actual strings, instead of the cells. "John C" wrote: As I stated in your other post that people still read, you cannot do what you are wanting to do. There is a 'way' that SUMIF can take multiple criteria, but it still won't work for your purposes. Check Dave's response to this post: http://www.microsoft.com/office/comm...7-197b6dc1d381 It shows you can have 2 values within the sumif, but it changes the formula to have an additional SUM around your SUMIF formula. Why are you adverse to changing the formula if it not ONLY achieves the desired result but is also easier to modify in the future? -- ** John C ** "RollieG" wrote: I originally asked: How do you use SUMIF, when your criteria is one of two(or multiple) values? For Example, I want to use: =SUMIF(A2:A10, ($C$1 or $D$1), B2:B10), but I know ($C$1 or $D$1) is not the correct syntax. To which I got this answer: Just add them: =SUMIF() + SUMIF() -- Gary''s Student - gsnu200810 This will not solve my dilemna. I replied to that with this, but i'm afraid the post will not be read. So I am re-asking the question. Thank you, but this is what I want to do: Here is the actual formula that I need to change: =IF(ISBLANK('29'!$AS$2:$AS$5000),"",SUMIF('29'!$AS $2:$AS$5000,$C$2,'29'!$AT$2:$AT$5000)). I want to use the Find and Replace function to replace the criteria "$C$2" with the desired expression ($C$2 or $K$1). I didn't originally design this worksheet, and I am stuck with the original logic. There are actually over 150 tabs with 31 formulas on each sheet to change. I wanted to use the Repace all function, selecting multiple sheets (tabs), so I can change all the formulas at one time. Changing it with your suggestion would mean, if I am understanding you correctly, would change the formula to : =IF(ISBLANK('29'!$AS$2:$AS$5000),"",SUMIF('29'!$AS $2:$AS$5000,$C$2,'29'!$AT$2:$AT$5000+SUMIF('29'!$A S$2:$AS$5000,$K$1,'29'!$AT$2:$AT$5000)) The Sheet tab '29" refers to a sheet with data from the 29th of the month. The next formula on the next row is: =IF(ISBLANK('30"AS$2:$AS$5000),"",SUMIF('30'S$2:$A S$5000,$C$2,'29'!$AT$2:$AT$5000)). So, I cannot fill down formulas. This is why I would like to change the formula using the Find and Replace method. I thought there could be an expression in the criteria part of the formula that can refer to one of two or more values. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Criteria Syntax in SUMIF formula
You can use a single SUMIF formula if you are willing to use a helper column.
Say A2 thru B10 contains: 1 2 1 3 1 4 2 5 2 6 2 7 3 8 3 9 3 10 With C1 containing 1 and D1 containing 2 In E2, we enter: =IF(OR(A2=$C$1,A2=$D$1),1,0) and copy down. The values in column E reflect BOTH criteria. This allows a single sumif formula: =SUMIF(E2:E10,1,B2:B10) -- Gary''s Student - gsnu200810 "RollieG" wrote: Because it will take a lot longer to change the formulas. Using a simple find and replace method would not take as long, and I would not have to try to enter this formula as an array formula. Dave Peterson's suggestion is what I would want to do, but it only seems to work when I enter the actual strings, instead of the cells. "John C" wrote: As I stated in your other post that people still read, you cannot do what you are wanting to do. There is a 'way' that SUMIF can take multiple criteria, but it still won't work for your purposes. Check Dave's response to this post: http://www.microsoft.com/office/comm...7-197b6dc1d381 It shows you can have 2 values within the sumif, but it changes the formula to have an additional SUM around your SUMIF formula. Why are you adverse to changing the formula if it not ONLY achieves the desired result but is also easier to modify in the future? -- ** John C ** "RollieG" wrote: I originally asked: How do you use SUMIF, when your criteria is one of two(or multiple) values? For Example, I want to use: =SUMIF(A2:A10, ($C$1 or $D$1), B2:B10), but I know ($C$1 or $D$1) is not the correct syntax. To which I got this answer: Just add them: =SUMIF() + SUMIF() -- Gary''s Student - gsnu200810 This will not solve my dilemna. I replied to that with this, but i'm afraid the post will not be read. So I am re-asking the question. Thank you, but this is what I want to do: Here is the actual formula that I need to change: =IF(ISBLANK('29'!$AS$2:$AS$5000),"",SUMIF('29'!$AS $2:$AS$5000,$C$2,'29'!$AT$2:$AT$5000)). I want to use the Find and Replace function to replace the criteria "$C$2" with the desired expression ($C$2 or $K$1). I didn't originally design this worksheet, and I am stuck with the original logic. There are actually over 150 tabs with 31 formulas on each sheet to change. I wanted to use the Repace all function, selecting multiple sheets (tabs), so I can change all the formulas at one time. Changing it with your suggestion would mean, if I am understanding you correctly, would change the formula to : =IF(ISBLANK('29'!$AS$2:$AS$5000),"",SUMIF('29'!$AS $2:$AS$5000,$C$2,'29'!$AT$2:$AT$5000+SUMIF('29'!$A S$2:$AS$5000,$K$1,'29'!$AT$2:$AT$5000)) The Sheet tab '29" refers to a sheet with data from the 29th of the month. The next formula on the next row is: =IF(ISBLANK('30"AS$2:$AS$5000),"",SUMIF('30'S$2:$A S$5000,$C$2,'29'!$AT$2:$AT$5000)). So, I cannot fill down formulas. This is why I would like to change the formula using the Find and Replace method. I thought there could be an expression in the criteria part of the formula that can refer to one of two or more values. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Criteria Syntax in SUMIF formula | Excel Discussion (Misc queries) | |||
Sumif Syntax | Excel Worksheet Functions | |||
Sumif Syntax | Excel Worksheet Functions | |||
SUMIF Formula w/ OR Criteria | Excel Discussion (Misc queries) | |||
Can a formula be used in the Criteria field of SUMIF?? | Excel Worksheet Functions |