=SUMPRODUCT(($A$1:$A$100="text1")*($B$1:$B$100="text2")*$C$1:$C$100)
Change ranges to suit, but ensure that they are of equal size.
--
Regards
Roger Govier
"SteveS" <Ste...@discussions.microsoft.com> wrote in message
news:85C6D69E-72C3-468E...@microsoft.com...
SP doesn't support full columns (not until Excel 200&), but specific ranges.
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"SteveS" <Ste...@discussions.microsoft.com> wrote in message
news:85C6D69E-72C3-468E...@microsoft.com...
=SUMPRODUCT(--(A1:A10="some_text"),--(B1:B10="some_text"),C1:C10)
Better to use cells to hold the criteria:
E1 = whatever
E2 = whatever
=SUMPRODUCT(--(A1:A10=E1),--(B1:B10=E2),C1:C10)
Biff
"SteveS" <Ste...@discussions.microsoft.com> wrote in message
news:85C6D69E-72C3-468E...@microsoft.com...
What is the advantage of this over Roger's formula?
--
Thanks,
Shane Devenshire
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Dave Peterson
If you have Excel 2002 or higher use the Evaluate Formula command and see
how many steps it takes each variation of the formula to calculate. I would
use a small data set for this, like 5-10 rows.
If you actually timed the calculation the difference would probably be
negligible on a small dataset. But on a large dataset there is a
considerable difference.
Screecap:
average calc times for 10 rows and 10,000 rows
http://img46.imageshack.us/img46/4064/calctimesax7.jpg
Biff
"ShaneDevenshire" <ShaneDe...@discussions.microsoft.com> wrote in
message news:82D641A0-10E7-42E7...@microsoft.com...
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"ShaneDevenshire" <ShaneDe...@discussions.microsoft.com> wrote in
message news:C07D27F9-14F2-4FA9...@microsoft.com...