SUMIF Function

Topic

New Reply

The SUMIF function adds a range of data if another range of data satisfies a truth condition. Is there a way to use a relative reference or a formula to define the truth condition rather than an input value?

Reply | Quote

Replies

Sure:

=SUMIF(B1:B12,E1,A1:A12)

This sums all values in A:A12 where B1:B12 is equal to the value in E1.

=SUMIF(B1:B12,”>” & E1,A1:A12)

This sums all values in A:A12 where B1:B12 is greater than the value in E1.

Reply | Quote

Thanks for your help. You have solved my problem.

Reply | Quote

Yes, within limits. For example you can’t use a boolean formula dynamically; sumif will just match against the formula result, and so will sum only values which are true, or false, or 1, or zero.

You can use sumif directly where the external reference is a straight match; where cell a1 contains UT, the comparison range contains US States, and the data range is numbers, =sumif(comparison_range,a1,data_range) will sum all numbers for UT.

And you can use sumif with externally referenced arguments using a little string formula to create a boolean; where cell a1 contains a variable to search against, you can use
=sumif(comparison_range,”<"&a1,data_range)

Care to post an example of what you want?

The SUMIF function adds a range of data if data within that range or a corresponding range satisfies a condition.

Reply | Quote

Another highly flexible approach is to use array formulae constructed in the form of:
=Sum(If(CompareRange operator condition),If(CompareRange2 operator condition2),Sumrange,0),0)).
This example would ‘AND’ the conditions. You could add the two ‘IF’ statements together to ‘OR’ the conditions. Look under TOOLS/Wizard for the Conditional Sum wizard. Play with this and see what formulae it builds for different circumstances and you should be able to get a good idea of how this works.
HTH,

Reply | Quote

Very cool, Gene, even if you are getting off the original =sumif() topic.

After messing around with them, I tend to stay away from array formulas on very large arrays because of their capacity to slow recalc. For a query this complex, I go to the =dfunction(data,offset,criteria) family.

Reply | Quote

off the topic????

I always thought SUM(IF was the same as SUMIF — just smarter

Reply | Quote

Thank you for your help. You have solved my problem.

Reply | Quote