Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SumIf Criteria Matches
I have two problems. First when I am doing a sumif formula, if I type =Sumif(A2:M2,"5",A2:M2) then my value is returned for the sum of whatever cells contain a number greater than 5. However if I want A1 to have the value to look for and I type =SumIf(A2:M2,"A1",A2:M2) I get a value of 0. I don't know what is wrong. Second I want to SumIf the criteria in A2:M2 is within 10% plus or minus of the value I type in A1. I don't know how to ask it to sum numbers that are within a plus or minus 10% of A1 or plus or minus 10% of 5. -- Debbie Dies ------------------------------------------------------------------------ Debbie Dies's Profile: http://www.excelforum.com/member.php...fo&userid=7894 View this thread: http://www.excelforum.com/showthread...hreadid=391932 |
#2
|
|||
|
|||
Maybe
=SumIf(A2:M2,"<"&1.1*A1,A2:M2) -SumIf(A2:M2,"<"&.9*A1,A2:M2) In other words, 1) the sum of everything less than 110% of A1 2) MINUS everything less than 90% of A1 Alternatively: =SUMPRODUCT(--(A2:M2(.9*a1)),--(A2:M2<(1.1*a1)),a2:m2) "Debbie Dies" wrote: I have two problems. First when I am doing a sumif formula, if I type =Sumif(A2:M2,"5",A2:M2) then my value is returned for the sum of whatever cells contain a number greater than 5. However if I want A1 to have the value to look for and I type =SumIf(A2:M2,"A1",A2:M2) I get a value of 0. I don't know what is wrong. Second I want to SumIf the criteria in A2:M2 is within 10% plus or minus of the value I type in A1. I don't know how to ask it to sum numbers that are within a plus or minus 10% of A1 or plus or minus 10% of 5. -- Debbie Dies ------------------------------------------------------------------------ Debbie Dies's Profile: http://www.excelforum.com/member.php...fo&userid=7894 View this thread: http://www.excelforum.com/showthread...hreadid=391932 |
#3
|
|||
|
|||
=SumIf(A2:M2,""&A1)
and =SUMPRODUCT(--(A2:M2A1*90%),--(A2:M2<=A1*110%),A2:M2) -- HTH RP (remove nothere from the email address if mailing direct) "Debbie Dies" wrote in message ... I have two problems. First when I am doing a sumif formula, if I type =Sumif(A2:M2,"5",A2:M2) then my value is returned for the sum of whatever cells contain a number greater than 5. However if I want A1 to have the value to look for and I type =SumIf(A2:M2,"A1",A2:M2) I get a value of 0. I don't know what is wrong. Second I want to SumIf the criteria in A2:M2 is within 10% plus or minus of the value I type in A1. I don't know how to ask it to sum numbers that are within a plus or minus 10% of A1 or plus or minus 10% of 5. -- Debbie Dies ------------------------------------------------------------------------ Debbie Dies's Profile: http://www.excelforum.com/member.php...fo&userid=7894 View this thread: http://www.excelforum.com/showthread...hreadid=391932 |
#4
|
|||
|
|||
"Bob Phillips" wrote...
.... =SUMPRODUCT(--(A2:M2A1*90%),--(A2:M2<=A1*110%),A2:M2) .... Or =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2) |
#5
|
|||
|
|||
Yep, I buy that.
-- HTH RP (remove nothere from the email address if mailing direct) "Harlan Grove" wrote in message ... "Bob Phillips" wrote... ... =SUMPRODUCT(--(A2:M2A1*90%),--(A2:M2<=A1*110%),A2:M2) ... Or =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use the sumif if I have multiple criteria (i.e. greater . | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
"criteria" in a sumif refering to the value in another cell | Excel Discussion (Misc queries) | |||
How do I ask for multiple criteria when creating a "sumif" formul. | Excel Worksheet Functions | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |