Sumifs - greater than criteria with a Cell Reference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dweid - 27 Mar 2008 20:48 GMT

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

Reply to this Message

Dave Peterson - 27 Mar 2008 20:59 GMT

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

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

> 2007 excel sumifs problem,
>
[quoted text clipped - 10 lines]
>
> David

Signature

Dave Peterson

Reply to this Message

Dweid - 27 Mar 2008 21:18 GMT

I have multiple criteria and ranges.

Anyway your answer worked ("<"&g6)

Thanks for your help.

> =sumif(criteriarange,"<"&g6,sumrange)
>
[quoted text clipped - 14 lines]
> >
> > David

Reply to this Message

PCLIVE - 27 Mar 2008 21:02 GMT

You could use SUMPRODUCT.

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

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

HTH,
Paul

> 2007 excel sumifs problem,
>
[quoted text clipped - 11 lines]
>
> David

Reply to this Message