SUMIFS formula where the criteria reference starts with ">"

[paulym666]

I'm using standard SUMIFS formulae to sum a set of data according to a "maturity bucket" field.

The summary I'm populating has maturity buckets as headers across the top:

Cell B2 contains "<=1year"
Cell C2 contains ">1year<=2years"
...
Cell J2 contains ">8years<=9years"
Cell K2 contains ">9years"

I'm populating the values with formulae that look like this (using Microsoft notation for clarity):

In cell B3 I have the formula =SUMIFS(sum_range, maturity_bucket, B2)
In cell C3 I have the formula =SUMIFS(sum_range, maturity_bucket, C2)
Etc

The values coming into the the summary are incorrect - all balances come through into column B, as if they are all in the <=1year" bucket, when in fact the data is spread across all the various buckets.

This seems to be caused by the presence of "<" at the beginning of the text of the header, cell B2, and ">" at the beginning of the other headers.

A simple solution would normally be to change the headers to avoid starting with "<" or ">". Unfortunately, it's forbidden to change the summary I'm populating (except to add formulae where the summarised data goes) so I can't take that approach. An inelegant solution would be to have an intermediate table with better headers which I can populate with SUMIFS and then link the final summary directly to that. But if possible I would like to find a cleaner solution - a way to amend the SUMIFS formulae above to handle the "<" and ">" without resorting to a separate intermediate version.

Any ideas?

Thanks!

 

[jasonb75]

Try using "~< or "~> at the start of the criteria. It works when you need to look for a * or ? instead of using them as wildcards but I've never had cause to try it with < or >

 

[Fluff]

Another option is

=SUMIFS(sum_range, maturity_bucket, "="&B2)

 

[paulym666]

Another option is

=SUMIFS(sum_range, maturity_bucket, "="&B2)

Great - that worked. Thanks

 

[Fluff]

Glad we could help & thanks for the feedback.