Sumifs - greater than criteria with a Cell Reference
 

2007 excel sumifs problem,

I am trying to us a sumif with multiple ranges and criteria but am having on
bug, with cell referenceing when using it with a greater or less than
criteria.

Example
sumifs(sum range, criteria range, <(G6))
where G6 equalls and input cell.
When I substitiute G6 with "<10" it works but when using a cell reference
instead of 10, nothing works.
Thanks.

David

Answer: Sumifs - greater than criteria with a Cell Reference
 

Hi David,

It sounds like you're having trouble using a cell reference with a greater than or less than criteria in your formula. Here's how you can fix it:

Instead of using the less than or greater than symbol in your criteria range, you can use the less than or greater than function in Excel. For example, if you want to sum all values in your sum range that are greater than the value in cell G6, you can use the following formula:

1. Code:

   ---

   =SUMIFS(sum range, criteria range, ""&G6)

   ---

In this formula, the ""&G6 concatenates the greater than symbol with the value in cell G6. This tells Excel to look for values in the sum range that are greater than the value in cell G6.

Similarly, if you want to sum all values in your sum range that are less than the value in cell G6, you can use the following formula:

1. Code:

   ---

   =SUMIFS(sum range, criteria range, "<"&G6)

   ---

In this formula, the "<"&G6 concatenates the less than symbol with the value in cell G6. This tells Excel to look for values in the sum range that are less than the value in cell G6.

Sumifs - greater than criteria with a Cell Reference
 

=sumif(criteriarange,"<"&g6,sumrange)

Not sure why you want to use =sumifs().



Dweid wrote:

2007 excel sumifs problem,

I am trying to us a sumif with multiple ranges and criteria but am having on
bug, with cell referenceing when using it with a greater or less than
criteria.

Example
sumifs(sum range, criteria range, <(G6))
where G6 equalls and input cell.
When I substitiute G6 with "<10" it works but when using a cell reference
instead of 10, nothing works.
Thanks.

David

--

Dave Peterson

Sumifs - greater than criteria with a Cell Reference
 

You could use SUMPRODUCT.

=SUMPRODUCT(--(Criteria_Range<G6),Sum_Range)

=SUMPRODUCT(--(A1:A20<G6),A1:A20)

HTH,
Paul


--

"Dweid" wrote in message
...
2007 excel sumifs problem,

I am trying to us a sumif with multiple ranges and criteria but am having
on
bug, with cell referenceing when using it with a greater or less than
criteria.

Example
sumifs(sum range, criteria range, <(G6))
where G6 equalls and input cell.
When I substitiute G6 with "<10" it works but when using a cell reference
instead of 10, nothing works.
Thanks.

David

Sumifs - greater than criteria with a Cell Reference
 

I have multiple criteria and ranges.

Anyway your answer worked ("<"&g6)

Thanks for your help.

"Dave Peterson" wrote:

=sumif(criteriarange,"<"&g6,sumrange)

Not sure why you want to use =sumifs().



Dweid wrote:

2007 excel sumifs problem,

I am trying to us a sumif with multiple ranges and criteria but am having on
bug, with cell referenceing when using it with a greater or less than
criteria.

Example
sumifs(sum range, criteria range, <(G6))
where G6 equalls and input cell.
When I substitiute G6 with "<10" it works but when using a cell reference
instead of 10, nothing works.
Thanks.

David

--

Dave Peterson