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?