# Sum if based on ranges of conditions

1. ## Sum if based on ranges of conditions

Hi all,

I need to do a SUMIF that is rather advanced compared to what I'm used to since it must compare arrays of different sizes (or match them sequentially). I've attached a workbook example.

I have an array A that consists of different types a, b, c, d, e, f.

Then an array B that contains ranges (lower and upper bounds) of unique identifiers that are connected with either a, b, c, d, e, or f (i.e. 1000 to 1099 belongs to a, 1100 to 1249 belongs to a, and 3000 to 3099 belongs to a, and so forth for other types).

Finally an array C that contains a very large data range of a given identifier and an associated value.

I need to do a sumif of the values in array C, given that the identifier is within any of the given ranges in array B that are associated with a given type.

Currently, my only solution is to split it up into several formulae parts, where I have 1 SUMIFS() for each identifier in array B that matches the type in array A. But this is unfeasible in my actual workbook and very manual.

Is there a way to do this in one formula? I've tried SUM(IF()) arrays, but that won't work as it usually does, since the arrays are not of the same size.

2. ## Re: Sum if based on ranges of conditions

It would be easier to use a helper column and fill that with the appropriate code, e.g. put this formula in L4:

=VLOOKUP(J4,\$E\$4:\$G\$14,3)

and copy that down to the bottom of your data list. Then you can use this in B4:

=SUMIF(L:L,A4,K:K)

and copy that down.

Hope this helps.

Pete

3. ## Re: Sum if based on ranges of conditions

Duplicated post.

Pete

4. ## Re: Sum if based on ranges of conditions

Hi Pete,

Thanks for your suggestion. That would work well, given that I had the option to add that column. Unfortunately, in the data I have, I need to calculate it directly as I cannot modify the source data.

5. ## Re: Sum if based on ranges of conditions

Originally Posted by thomasms90
... I need to calculate it directly as I cannot modify the source data ...
Okay, well good luck with that - I've given you a solution which is simple and robust.

Pete

There are currently 1 users browsing this thread. (0 members and 1 guests)