| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Criteria Syntax in SUMIF formula
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria Syntax in SUMIF formula
Just add them:
=SUMIF() + SUMIF() -- Gary''s Student - gsnu200810 "RollieG" wrote: 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria Syntax in SUMIF formula
As a followup, since we don't know how the values in $C$1 and $D$1 are
generated, is there ever a chance they will be equal to each other? If so, you could modify Gary's suggestion like so: =(SUMIF(...)+SUMIF(...))/(1+($C$1=$D$1)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Gary''s Student" wrote: Just add them: =SUMIF() + SUMIF() -- Gary''s Student - gsnu200810 "RollieG" wrote: 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria Syntax in SUMIF formula
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)). 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 refers to one of two or more values. "Gary''s Student" wrote: Just add them: =SUMIF() + SUMIF() -- Gary''s Student - gsnu200810 "RollieG" wrote: 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria Syntax in SUMIF formula
You could always change up the formula a little bit. Assuming you have the
day of the month in a column, you could always combine the formula with indirects: =IF(ISBLANK(INDIRECT("'"&A10&"'!$AS$2:$AS$5000")), "",SUMIF(INDIRECT("'"&A10&"'!$AS$2:$AS$5000"),$C$2 ,INDIRECT("'"&A10&"'!$AT$2:$AT$5000"))+SUMIF(INDIR ECT("'"&A10&"'!$AS$2:$AS$5000"),$K$1,INDIRECT("'"& A10&"'!$AT$2:$AT$5000"))) This would be the formula for row 10 (assuming you have header information above like in C2 and K1. This also assumes your day numbering is in column A. Modify as needed. You could also use different methods to generate the tab name, for example, instead of A10 for the 1 tab, you could use ROW()-9 in place, and that will evaluate to 1. -- ** John C ** "RollieG" wrote: 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)). 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 refers to one of two or more values. "Gary''s Student" wrote: Just add them: =SUMIF() + SUMIF() -- Gary''s Student - gsnu200810 "RollieG" wrote: 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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria Syntax in SUMIF formula
NOTE: This is an **array** formula, and therefore must be entered using
CTRL+Shift+Enter -- ** John C ** "John C" wrote: You could always change up the formula a little bit. Assuming you have the day of the month in a column, you could always combine the formula with indirects: =IF(ISBLANK(INDIRECT("'"&A10&"'!$AS$2:$AS$5000")), "",SUMIF(INDIRECT("'"&A10&"'!$AS$2:$AS$5000"),$C$2 ,INDIRECT("'"&A10&"'!$AT$2:$AT$5000"))+SUMIF(INDIR ECT("'"&A10&"'!$AS$2:$AS$5000"),$K$1,INDIRECT("'"& A10&"'!$AT$2:$AT$5000"))) This would be the formula for row 10 (assuming you have header information above like in C2 and K1. This also assumes your day numbering is in column A. Modify as needed. You could also use different methods to generate the tab name, for example, instead of A10 for the 1 tab, you could use ROW()-9 in place, and that will evaluate to 1. -- ** John C ** "RollieG" wrote: 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)). 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 refers to one of two or more values. "Gary''s Student" wrote: Just add them: =SUMIF() + SUMIF() -- Gary''s Student - gsnu200810 "RollieG" wrote: 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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria Syntax in SUMIF formula
Thank you, but I just wanted to know one thing:
Can the criteria in a SUMIF refer to one of two (or more) values? "John C" wrote: You could always change up the formula a little bit. Assuming you have the day of the month in a column, you could always combine the formula with indirects: =IF(ISBLANK(INDIRECT("'"&A10&"'!$AS$2:$AS$5000")), "",SUMIF(INDIRECT("'"&A10&"'!$AS$2:$AS$5000"),$C$2 ,INDIRECT("'"&A10&"'!$AT$2:$AT$5000"))+SUMIF(INDIR ECT("'"&A10&"'!$AS$2:$AS$5000"),$K$1,INDIRECT("'"& A10&"'!$AT$2:$AT$5000"))) This would be the formula for row 10 (assuming you have header information above like in C2 and K1. This also assumes your day numbering is in column A. Modify as needed. You could also use different methods to generate the tab name, for example, instead of A10 for the 1 tab, you could use ROW()-9 in place, and that will evaluate to 1. -- ** John C ** "RollieG" wrote: 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)). 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 refers to one of two or more values. "Gary''s Student" wrote: Just add them: =SUMIF() + SUMIF() -- Gary''s Student - gsnu200810 "RollieG" wrote: 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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria Syntax in SUMIF formula
It can, sort of, but not in the way you want. Your overall formula will have
to be modified. -- ** John C ** "RollieG" wrote: Thank you, but I just wanted to know one thing: Can the criteria in a SUMIF refer to one of two (or more) values? "John C" wrote: You could always change up the formula a little bit. Assuming you have the day of the month in a column, you could always combine the formula with indirects: =IF(ISBLANK(INDIRECT("'"&A10&"'!$AS$2:$AS$5000")), "",SUMIF(INDIRECT("'"&A10&"'!$AS$2:$AS$5000"),$C$2 ,INDIRECT("'"&A10&"'!$AT$2:$AT$5000"))+SUMIF(INDIR ECT("'"&A10&"'!$AS$2:$AS$5000"),$K$1,INDIRECT("'"& A10&"'!$AT$2:$AT$5000"))) This would be the formula for row 10 (assuming you have header information above like in C2 and K1. This also assumes your day numbering is in column A. Modify as needed. You could also use different methods to generate the tab name, for example, instead of A10 for the 1 tab, you could use ROW()-9 in place, and that will evaluate to 1. -- ** John C ** "RollieG" wrote: 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)). 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 refers to one of two or more values. "Gary''s Student" wrote: Just add them: =SUMIF() + SUMIF() -- Gary''s Student - gsnu200810 "RollieG" wrote: 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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria Syntax in SUMIF formula
Try something like this:
=SUM(SUMIF(A2:A10,T(INDIRECT({"C1","D1"})),B2:B10) ) This assumes that the values in C1 and D1 are Text. If they're numeric, replace the "T" with an "N" (no quotes). Applying this concept to your formula, see if this works for you: IF(ISBLANK('29'!$AS$2:$AS$5000),"",SUM(SUMIF('29'! $AS$2:$AS$5000,T(INDIRECT({"C2","K1"})),'29'!$AT$2 :$AT$5000))) Don't forget about the "T" or the "N" to define the contents of C2 or K1. Also, since they're within the Indirect() function, you don't need the absolute $'s for C2 and K1. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RollieG" wrote in message ... Thank you, but I just wanted to know one thing: Can the criteria in a SUMIF refer to one of two (or more) values? "John C" wrote: You could always change up the formula a little bit. Assuming you have the day of the month in a column, you could always combine the formula with indirects: =IF(ISBLANK(INDIRECT("'"&A10&"'!$AS$2:$AS$5000")), "",SUMIF(INDIRECT("'"&A10&"'!$AS$2:$AS$5000"),$C$2 ,INDIRECT("'"&A10&"'!$AT$2:$AT$5000"))+SUMIF(INDIR ECT("'"&A10&"'!$AS$2:$AS$5000"),$K$1,INDIRECT("'"& A10&"'!$AT$2:$AT$5000"))) This would be the formula for row 10 (assuming you have header information above like in C2 and K1. This also assumes your day numbering is in column A. Modify as needed. You could also use different methods to generate the tab name, for example, instead of A10 for the 1 tab, you could use ROW()-9 in place, and that will evaluate to 1. -- ** John C ** "RollieG" wrote: 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)). 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 refers to one of two or more values. "Gary''s Student" wrote: Just add them: =SUMIF() + SUMIF() -- Gary''s Student - gsnu200810 "RollieG" wrote: 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. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria Syntax in SUMIF formula
To answer your basic question about multiple criteria for the Sumif()
function, the answer is *yes*. You wrap the Sumif() in the Sum() function, and you use an array constant for the criteria: =Sum(Sumif(A1:A10,{25,50,75},B1:B10)) =Sum(Sumif(A1:A10,{"Tom","Dick","Harry"},B1:B10)) However, array constants will *not* accept cell references. You therefore must include the Indirect() function to use cell references. As in my earlier example: =SUM(SUMIF(A2:A10,T(INDIRECT({"C1","D1"})),B2:B10) ) Using either a "T" or "N" to denote the contents of the cells being used as the criteria. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RollieG" wrote in message ... Thank you, but I just wanted to know one thing: Can the criteria in a SUMIF refer to one of two (or more) values? "John C" wrote: You could always change up the formula a little bit. Assuming you have the day of the month in a column, you could always combine the formula with indirects: =IF(ISBLANK(INDIRECT("'"&A10&"'!$AS$2:$AS$5000")), "",SUMIF(INDIRECT("'"&A10&"'!$AS$2:$AS$5000"),$C$2 ,INDIRECT("'"&A10&"'!$AT$2:$AT$5000"))+SUMIF(INDIR ECT("'"&A10&"'!$AS$2:$AS$5000"),$K$1,INDIRECT("'"& A10&"'!$AT$2:$AT$5000"))) This would be the formula for row 10 (assuming you have header information above like in C2 and K1. This also assumes your day numbering is in column A. Modify as needed. You could also use different methods to generate the tab name, for example, instead of A10 for the 1 tab, you could use ROW()-9 in place, and that will evaluate to 1. -- ** John C ** "RollieG" wrote: 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)). 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 refers to one of two or more values. "Gary''s Student" wrote: Just add them: =SUMIF() + SUMIF() -- Gary''s Student - gsnu200810 "RollieG" wrote: 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. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria Syntax in SUMIF formula
The issue the OP wants to do is to be a FIND/REPLACE for his formulas. Which
because the SUMIF formula must be wrapped in a SUM formula, will not work. -- ** John C ** "RagDyeR" wrote: To answer your basic question about multiple criteria for the Sumif() function, the answer is *yes*. You wrap the Sumif() in the Sum() function, and you use an array constant for the criteria: =Sum(Sumif(A1:A10,{25,50,75},B1:B10)) =Sum(Sumif(A1:A10,{"Tom","Dick","Harry"},B1:B10)) However, array constants will *not* accept cell references. You therefore must include the Indirect() function to use cell references. As in my earlier example: =SUM(SUMIF(A2:A10,T(INDIRECT({"C1","D1"})),B2:B10) ) Using either a "T" or "N" to denote the contents of the cells being used as the criteria. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RollieG" wrote in message ... Thank you, but I just wanted to know one thing: Can the criteria in a SUMIF refer to one of two (or more) values? "John C" wrote: You could always change up the formula a little bit. Assuming you have the day of the month in a column, you could always combine the formula with indirects: =IF(ISBLANK(INDIRECT("'"&A10&"'!$AS$2:$AS$5000")), "",SUMIF(INDIRECT("'"&A10&"'!$AS$2:$AS$5000"),$C$2 ,INDIRECT("'"&A10&"'!$AT$2:$AT$5000"))+SUMIF(INDIR ECT("'"&A10&"'!$AS$2:$AS$5000"),$K$1,INDIRECT("'"& A10&"'!$AT$2:$AT$5000"))) This would be the formula for row 10 (assuming you have header information above like in C2 and K1. This also assumes your day numbering is in column A. Modify as needed. You could also use different methods to generate the tab name, for example, instead of A10 for the 1 tab, you could use ROW()-9 in place, and that will evaluate to 1. -- ** John C ** "RollieG" wrote: 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)). 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 refers to one of two or more values. "Gary''s Student" wrote: Just add them: =SUMIF() + SUMIF() -- Gary''s Student - gsnu200810 "RollieG" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 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 | |||
| Multiple Criteria in a SUMIF formula | Excel Discussion (Misc queries) | |||
Linear Mode
