[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

select based on 2 columns?

Kent McPherson

12/15/2006 5:45:00 PM

I have a spreadsheet with many rows. I want to sum a column based on the
contents of another column. No problem. With this formula, I can do that:

=SUMIF(Results,"2-In progress",TCV) where Results and TCV are named
ranges.

Now I want to complicate this by adding another layer of filtering. For all
cells that match the above criterion, I want to add another test that says I
only want a subset of these records that match a string in another column.
So I want to add the TCV for all cells that match "xxx" in the range Results
further refined by matching the cells that match "yyy" in the range Sector.

I have tried many variations on formulas but can't seem to find a
combination that works. Any idea?
7 Answers

Kent McPherson

12/15/2006 7:25:00 PM

0

I tried this:

=SUMPRODUCT((Results="2-In progress"),(Sector="I"),TCV)

but I get a #NUM! error.

I can't use autofilters because this has to work without any human
intervention.

"Tom Ogilvy" <TomOgilvy@discussions.microsoft.com> wrote in message
news:366817E1-09C4-460F-9CE3-A2458948644B@microsoft.com...
> =sumproduct(--(Results="xxx"),--(Sector="yyy"),TCV)
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Kent McPherson" wrote:
>
>> I have a spreadsheet with many rows. I want to sum a column based on the
>> contents of another column. No problem. With this formula, I can do
>> that:
>>
>> =SUMIF(Results,"2-In progress",TCV) where Results and TCV are named
>> ranges.
>>
>> Now I want to complicate this by adding another layer of filtering. For
>> all
>> cells that match the above criterion, I want to add another test that
>> says I
>> only want a subset of these records that match a string in another
>> column.
>> So I want to add the TCV for all cells that match "xxx" in the range
>> Results
>> further refined by matching the cells that match "yyy" in the range
>> Sector.
>>
>> I have tried many variations on formulas but can't seem to find a
>> combination that works. Any idea?
>>
>>
>>
>>


Tom Ogilvy

12/15/2006 8:18:00 PM

0

Well, that wasn't the example I gave. If you had used my example you would
have had a formula like this:

=SUMPRODUCT(--(Results="2-In progress"),--(Sector="I"),TCV)

Which worked fine for me with some test data.

--
Regards,
Tom Ogilvy


"Kent McPherson" wrote:

> I tried this:
>
> =SUMPRODUCT((Results="2-In progress"),(Sector="I"),TCV)
>
> but I get a #NUM! error.
>
> I can't use autofilters because this has to work without any human
> intervention.
>
> "Tom Ogilvy" <TomOgilvy@discussions.microsoft.com> wrote in message
> news:366817E1-09C4-460F-9CE3-A2458948644B@microsoft.com...
> > =sumproduct(--(Results="xxx"),--(Sector="yyy"),TCV)
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Kent McPherson" wrote:
> >
> >> I have a spreadsheet with many rows. I want to sum a column based on the
> >> contents of another column. No problem. With this formula, I can do
> >> that:
> >>
> >> =SUMIF(Results,"2-In progress",TCV) where Results and TCV are named
> >> ranges.
> >>
> >> Now I want to complicate this by adding another layer of filtering. For
> >> all
> >> cells that match the above criterion, I want to add another test that
> >> says I
> >> only want a subset of these records that match a string in another
> >> column.
> >> So I want to add the TCV for all cells that match "xxx" in the range
> >> Results
> >> further refined by matching the cells that match "yyy" in the range
> >> Sector.
> >>
> >> I have tried many variations on formulas but can't seem to find a
> >> combination that works. Any idea?
> >>
> >>
> >>
> >>
>
>
>

Kent McPherson

12/15/2006 8:27:00 PM

0

Tom,

I coped your formula (which has the 2 minus signs before the 1st 2 range
arguments) into my spreadsheet and I still get the #NUM! error. Other
thoughts?

What do the 2 minus signs do?

Thanks for your help!
Kent

"Tom Ogilvy" <TomOgilvy@discussions.microsoft.com> wrote in message
news:FBF8B24B-AAB4-4E81-BF20-0079F4484CBC@microsoft.com...
> Well, that wasn't the example I gave. If you had used my example you
> would
> have had a formula like this:
>
> =SUMPRODUCT(--(Results="2-In progress"),--(Sector="I"),TCV)
>
> Which worked fine for me with some test data.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Kent McPherson" wrote:
>
>> I tried this:
>>
>> =SUMPRODUCT((Results="2-In progress"),(Sector="I"),TCV)
>>
>> but I get a #NUM! error.
>>
>> I can't use autofilters because this has to work without any human
>> intervention.
>>
>> "Tom Ogilvy" <TomOgilvy@discussions.microsoft.com> wrote in message
>> news:366817E1-09C4-460F-9CE3-A2458948644B@microsoft.com...
>> > =sumproduct(--(Results="xxx"),--(Sector="yyy"),TCV)
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "Kent McPherson" wrote:
>> >
>> >> I have a spreadsheet with many rows. I want to sum a column based on
>> >> the
>> >> contents of another column. No problem. With this formula, I can do
>> >> that:
>> >>
>> >> =SUMIF(Results,"2-In progress",TCV) where Results and TCV are named
>> >> ranges.
>> >>
>> >> Now I want to complicate this by adding another layer of filtering.
>> >> For
>> >> all
>> >> cells that match the above criterion, I want to add another test that
>> >> says I
>> >> only want a subset of these records that match a string in another
>> >> column.
>> >> So I want to add the TCV for all cells that match "xxx" in the range
>> >> Results
>> >> further refined by matching the cells that match "yyy" in the range
>> >> Sector.
>> >>
>> >> I have tried many variations on formulas but can't seem to find a
>> >> combination that works. Any idea?
>> >>
>> >>
>> >>
>> >>
>>
>>
>>


Tom Ogilvy

12/15/2006 8:50:00 PM

0

The two minus signs convert the boolean result of the logical comparison to
the numbers 1 for true or 0 for false so they can be multiplied by Sumproduct
to determine whether to add in the value for each row of TCV.

Do you have a #Num error in your source data?

This formula is pretty much standard fare, so if your having problems it
likely isn't the formula and as I said, it is working masterfully for me.

--
Regards,
Tom Ogilvy


"Kent McPherson" wrote:

> Tom,
>
> I coped your formula (which has the 2 minus signs before the 1st 2 range
> arguments) into my spreadsheet and I still get the #NUM! error. Other
> thoughts?
>
> What do the 2 minus signs do?
>
> Thanks for your help!
> Kent
>
> "Tom Ogilvy" <TomOgilvy@discussions.microsoft.com> wrote in message
> news:FBF8B24B-AAB4-4E81-BF20-0079F4484CBC@microsoft.com...
> > Well, that wasn't the example I gave. If you had used my example you
> > would
> > have had a formula like this:
> >
> > =SUMPRODUCT(--(Results="2-In progress"),--(Sector="I"),TCV)
> >
> > Which worked fine for me with some test data.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Kent McPherson" wrote:
> >
> >> I tried this:
> >>
> >> =SUMPRODUCT((Results="2-In progress"),(Sector="I"),TCV)
> >>
> >> but I get a #NUM! error.
> >>
> >> I can't use autofilters because this has to work without any human
> >> intervention.
> >>
> >> "Tom Ogilvy" <TomOgilvy@discussions.microsoft.com> wrote in message
> >> news:366817E1-09C4-460F-9CE3-A2458948644B@microsoft.com...
> >> > =sumproduct(--(Results="xxx"),--(Sector="yyy"),TCV)
> >> >
> >> > --
> >> > Regards,
> >> > Tom Ogilvy
> >> >
> >> >
> >> > "Kent McPherson" wrote:
> >> >
> >> >> I have a spreadsheet with many rows. I want to sum a column based on
> >> >> the
> >> >> contents of another column. No problem. With this formula, I can do
> >> >> that:
> >> >>
> >> >> =SUMIF(Results,"2-In progress",TCV) where Results and TCV are named
> >> >> ranges.
> >> >>
> >> >> Now I want to complicate this by adding another layer of filtering.
> >> >> For
> >> >> all
> >> >> cells that match the above criterion, I want to add another test that
> >> >> says I
> >> >> only want a subset of these records that match a string in another
> >> >> column.
> >> >> So I want to add the TCV for all cells that match "xxx" in the range
> >> >> Results
> >> >> further refined by matching the cells that match "yyy" in the range
> >> >> Sector.
> >> >>
> >> >> I have tried many variations on formulas but can't seem to find a
> >> >> combination that works. Any idea?
> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Ken

12/15/2006 10:16:00 PM

0

Kent

Tom's formula works quite well for me too. If products, sectors, and
tcv do not all have the same number of rows, I get a #value error, but,
other than that this seems to be a very robust formula. Until a few
minutes ago (when I read Tom's explanation of the --) I would have
solved your problem with and array sum function, like

{=SUM((Results="xxx")*(Sector="yyy")*TCV)}

which has both the advantages and disadvantages inherent in any array
functions, also requires the columns to be the same length, and unlike
Tom's sumproduct recommendation, it breaks when TCV is not all numeric.
I don't know about you, but, I think I will be using sumproduct a lot
more often now that I know what the -- does.

Thanks Tom, and good luck Kent.

Ken




Tom Ogilvy wrote:
> =sumproduct(--(Results="xxx"),--(Sector="yyy"),TCV)
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Kent McPherson" wrote:
>
> > I have a spreadsheet with many rows. I want to sum a column based on the
> > contents of another column. No problem. With this formula, I can do that:
> >
> > =SUMIF(Results,"2-In progress",TCV) where Results and TCV are named
> > ranges.
> >
> > Now I want to complicate this by adding another layer of filtering. For all
> > cells that match the above criterion, I want to add another test that says I
> > only want a subset of these records that match a string in another column.
> > So I want to add the TCV for all cells that match "xxx" in the range Results
> > further refined by matching the cells that match "yyy" in the range Sector.
> >
> > I have tried many variations on formulas but can't seem to find a
> > combination that works. Any idea?
> >
> >
> >
> >

Chip Pearson

12/17/2006 2:26:00 AM

0

Just for the record, Excel 2007 has a function called SUMIFS that is like
SUMIF but allows for multiple criteria.

=SUMIFS(TCV,Results,"2-In progress")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Kent McPherson" <mcpherk@us.ibm.com> wrote in message
news:up2$9CHIHHA.3952@TK2MSFTNGP02.phx.gbl...
>I have a spreadsheet with many rows. I want to sum a column based on the
>contents of another column. No problem. With this formula, I can do
>that:
>
> =SUMIF(Results,"2-In progress",TCV) where Results and TCV are named
> ranges.
>
> Now I want to complicate this by adding another layer of filtering. For
> all cells that match the above criterion, I want to add another test that
> says I only want a subset of these records that match a string in another
> column. So I want to add the TCV for all cells that match "xxx" in the
> range Results further refined by matching the cells that match "yyy" in
> the range Sector.
>
> I have tried many variations on formulas but can't seem to find a
> combination that works. Any idea?
>
>
>


Kent McPherson

12/18/2006 5:15:00 PM

0

Tom, thanks very much! I figured out why mine version didn't work. The
named ranges I was using in the formula (Results, TCV, & Sector) referred to
a specific column in another worksheet. When I changed the references to
specific cells within those columns, the formula worked like a charm.

Thank you so much!

"Tom Ogilvy" <TomOgilvy@discussions.microsoft.com> wrote in message
news:1163BBA5-8A22-4BFB-8420-B71339E288A2@microsoft.com...
> The two minus signs convert the boolean result of the logical comparison
> to
> the numbers 1 for true or 0 for false so they can be multiplied by
> Sumproduct
> to determine whether to add in the value for each row of TCV.
>
> Do you have a #Num error in your source data?
>
> This formula is pretty much standard fare, so if your having problems it
> likely isn't the formula and as I said, it is working masterfully for me.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Kent McPherson" wrote:
>
>> Tom,
>>
>> I coped your formula (which has the 2 minus signs before the 1st 2 range
>> arguments) into my spreadsheet and I still get the #NUM! error. Other
>> thoughts?
>>
>> What do the 2 minus signs do?
>>
>> Thanks for your help!
>> Kent
>>
>> "Tom Ogilvy" <TomOgilvy@discussions.microsoft.com> wrote in message
>> news:FBF8B24B-AAB4-4E81-BF20-0079F4484CBC@microsoft.com...
>> > Well, that wasn't the example I gave. If you had used my example you
>> > would
>> > have had a formula like this:
>> >
>> > =SUMPRODUCT(--(Results="2-In progress"),--(Sector="I"),TCV)
>> >
>> > Which worked fine for me with some test data.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "Kent McPherson" wrote:
>> >
>> >> I tried this:
>> >>
>> >> =SUMPRODUCT((Results="2-In progress"),(Sector="I"),TCV)
>> >>
>> >> but I get a #NUM! error.
>> >>
>> >> I can't use autofilters because this has to work without any human
>> >> intervention.
>> >>
>> >> "Tom Ogilvy" <TomOgilvy@discussions.microsoft.com> wrote in message
>> >> news:366817E1-09C4-460F-9CE3-A2458948644B@microsoft.com...
>> >> > =sumproduct(--(Results="xxx"),--(Sector="yyy"),TCV)
>> >> >
>> >> > --
>> >> > Regards,
>> >> > Tom Ogilvy
>> >> >
>> >> >
>> >> > "Kent McPherson" wrote:
>> >> >
>> >> >> I have a spreadsheet with many rows. I want to sum a column based
>> >> >> on
>> >> >> the
>> >> >> contents of another column. No problem. With this formula, I can
>> >> >> do
>> >> >> that:
>> >> >>
>> >> >> =SUMIF(Results,"2-In progress",TCV) where Results and TCV are
>> >> >> named
>> >> >> ranges.
>> >> >>
>> >> >> Now I want to complicate this by adding another layer of filtering.
>> >> >> For
>> >> >> all
>> >> >> cells that match the above criterion, I want to add another test
>> >> >> that
>> >> >> says I
>> >> >> only want a subset of these records that match a string in another
>> >> >> column.
>> >> >> So I want to add the TCV for all cells that match "xxx" in the
>> >> >> range
>> >> >> Results
>> >> >> further refined by matching the cells that match "yyy" in the range
>> >> >> Sector.
>> >> >>
>> >> >> I have tried many variations on formulas but can't seem to find a
>> >> >> combination that works. Any idea?
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>