SumIf Criteria Matches

[Debbie Dies]

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.

[Duke Carey]

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
>
>

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Duke Carey]

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
>
>

[Duke Carey]

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
>
>

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Duke Carey]

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
>
>

[Duke Carey]

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
>
>

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Duke Carey]

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
>
>

[Duke Carey]

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
>
>

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Duke Carey]

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
>
>

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Duke Carey]

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
>
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Duke Carey]

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
>
>

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Duke Carey]

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
>
>

[Duke Carey]

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
>
>

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Duke Carey]

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
>
>

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Duke Carey]

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
>
>

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Duke Carey]

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
>
>

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>

[Duke Carey]

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
>
>

[Bob Phillips]

=SumIf(A2:M2,">"&A1)

and

=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Debbie Dies" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Harlan Grove]

"Bob Phillips" <[email protected]> wrote...
....
>=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
....

Or

=SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)

[Duke Carey]

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
>
>

[Bob Phillips]

Yep, I buy that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> ...
> >=SUMPRODUCT(--(A2:M2>A1*90%),--(A2:M2<=A1*110%),A2:M2)
> ...
>
> Or
>
> =SUMPRODUCT(--(ABS(A2:M2/A1-1)<=0.1),A2:M2)
>
>