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?

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" wrote in message
...
=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,

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" wrote in message
...
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" wrote in message
...
=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

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?

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" wrote in message
...
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, 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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
=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?