PC Review


Reply
Thread Tools Rate Thread

sumif with multiple critera range and sum_range

 
 
DocBrown
Guest
Posts: n/a
 
      20th Dec 2008
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),"="&C47,($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

 
Reply With Quote
 
 
 
 
Bernard Liengme
Guest
Posts: n/a
 
      20th Dec 2008
For details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"DocBrown" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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),"="&C47,($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
>



 
Reply With Quote
 
 
 
 
T. Valko
Guest
Posts: n/a
 
      21st Dec 2008
>=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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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),"="&C47,($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
>



 
Reply With Quote
 
DocBrown
Guest
Posts: n/a
 
      21st Dec 2008
Hi Bernard,

This looks promising, but I think I need a bit more hand holding. Example 4
looks similar, but I don't see the whole picture. I'll expand on my concept
and see if that changes the answer. The cell layout is:

J K L O P Q T U V
31 one text 5 two text 7 four text 9
32 two text 6 three text 8 two text 10
33
34

C74 is the value I'm matching if it = "two"
then sumif returns the sum of L32, Q31, V32.

Will the SUMPRODUCT be able to do this?

Thanks,
John

"Bernard Liengme" wrote:

> For details on SUMPRODUCT
> Bob Phillips
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> J.E McGimpsey
> http://mcgimpsey.com/excel/formulae/doubleneg.html
>
> --
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
>
> "DocBrown" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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),"="&C47,($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
> >

>
>
>

 
Reply With Quote
 
DocBrown
Guest
Posts: n/a
 
      21st Dec 2008
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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),"="&C47,($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
> >

>
>
>

 
Reply With Quote
 
T. Valko
Guest
Posts: n/a
 
      21st Dec 2008
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"DocBrown" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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),"="&C47,($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
>> >

>>
>>
>>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumif with multiple columns in sum_range dza7 Microsoft Excel Misc 19 23rd Dec 2009 01:47 PM
SumIf( range, criteria, sum_range ) andré C. Microsoft Excel Misc 3 2nd Mar 2009 02:51 AM
SumIf ( range, criteria, sum_range ) andré C. Microsoft Excel Programming 1 1st Mar 2009 08:37 PM
Sumif with multiple columns in sum_range dza7 Microsoft Excel Programming 17 28th Mar 2008 08:25 PM
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function Oscar Microsoft Excel Worksheet Functions 2 12th Jan 2005 12:01 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:24 AM.