Sumproduct/SumIf
 

Would appreciate help with the following: Sum column B where name in A:A =
A1 and B:B < 10.

Result for John should be 9. Once formula points to Mark, his total should
be 14. Tried SumProduct, but I'm only getting the count of rows that meet
criteria, not the sum.


A B
John 10
John 09
Mark 3
Mark 4
Mark 7

Many thanks!
--
T

Sumproduct/SumIf
 

Try this:

=SUMPRODUCT(--(A1:A10=A1),--(B1:B10<10),B1:B10)

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.

--
Biff
Microsoft Excel MVP


"T" wrote in message
...
Would appreciate help with the following: Sum column B where name in A:A
=
A1 and B:B < 10.

Result for John should be 9. Once formula points to Mark, his total
should
be 14. Tried SumProduct, but I'm only getting the count of rows that meet
criteria, not the sum.


A B
John 10
John 09
Mark 3
Mark 4
Mark 7

Many thanks!
--
T

Sumproduct/SumIf
 

try
=sumproduct((a2:a22=a1)*(b2:b22<10)*b2:b22)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"T" wrote in message
...
Would appreciate help with the following: Sum column B where name in A:A
=
A1 and B:B < 10.

Result for John should be 9. Once formula points to Mark, his total
should
be 14. Tried SumProduct, but I'm only getting the count of rows that meet
criteria, not the sum.


A B
John 10
John 09
Mark 3
Mark 4
Mark 7

Many thanks!
--
T

Sumproduct/SumIf
 

Does this help,

=SUMPRODUCT(--(A1:A10=A1),--(B1:B10<10),B1:B10)

Regards,
Paul

--

"T" wrote in message
...
Would appreciate help with the following: Sum column B where name in A:A
=
A1 and B:B < 10.

Result for John should be 9. Once formula points to Mark, his total
should
be 14. Tried SumProduct, but I'm only getting the count of rows that meet
criteria, not the sum.


A B
John 10
John 09
Mark 3
Mark 4
Mark 7

Many thanks!
--
T

Sumproduct/SumIf
 

=SUMPRODUCT(--(A2:A10=A1),--(B2:B10<10),(B2:B10))

Remember, unless you have xl2007, you should refrain from referencing whole
columns.

Hope this helps.
--
John C


"T" wrote:

Would appreciate help with the following: Sum column B where name in A:A =
A1 and B:B < 10.

Result for John should be 9. Once formula points to Mark, his total should
be 14. Tried SumProduct, but I'm only getting the count of rows that meet
criteria, not the sum.


A B
John 10
John 09
Mark 3
Mark 4
Mark 7

Many thanks!
--
T

Sumproduct/SumIf
 

sum

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
=SUMPRODUCT(--(A2:A10=A1),--(B2:B10<10),(B2:B10))

Remember, unless you have xl2007, you should refrain from referencing
whole
columns.

Hope this helps.
--
John C


"T" wrote:

Would appreciate help with the following: Sum column B where name in A:A
=
A1 and B:B < 10.

Result for John should be 9. Once formula points to Mark, his total
should
be 14. Tried SumProduct, but I'm only getting the count of rows that
meet
criteria, not the sum.


A B
John 10
John 09
Mark 3
Mark 4
Mark 7

Many thanks!
--
T