You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"DocBrown" wrote in message
...
That is awsome! It works like a charm. I didn't think the ranges would
work
like that

Thank you a bunch.

John

"T. Valko" wrote:

=SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="& C47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36))

Although Excel accepts the formula like that each "middle" range is being
ignored. That formula can be written like this and will produce the same
result:

=SUMIF($J$31:$T$36,C47,$L$31:$V$36)

Here is the pattern it's following:

Criteria range.....Sum range
............J....................L
............K..................M
............N..................P
............O..................Q
............R...................T
............S...................U
............V.....................

If you want to restructure your setup so you can squeeze in 12 months
worth
of data in the least amount of space then use every other column.

C = criteria range
S = sum range

C...S...C...S...C...S...C...S etc
J....K...L...M..N..O...P...Q etc

Then the offset in the formula is 1:

=SUMIF($J$31:$P$36,C47,$K$31:$Q$36)


--
Biff
Microsoft Excel MVP


"DocBrown" wrote in message
...
Hello,

I have a worksheet where I'm trying to use the sumif function to sum
over
multiple criteria ranges and multiple sum_ranges. The following
formula,
which shows three ranges, (surprisingly) works. Is there a more compact
way
to implement this? This is for a budget spreadsheet where each of the
ranges
(J31:J36 for example, are one month. So the full scale formula would
have
12
ranges and sum_ranges. Notice that the sum_ranges are offset two
columns
right of the critera range.

=SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C 47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36))

If I rearranged the columns, would it that make it possible for a more
compact formula to work?

Thanks,
John