Sumproduct/SumIf

T

T

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

T. Valko

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.
 
J

John C

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

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

Hope this helps.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Variable Sumproduct Range 6
COUNTIF? SUMIF? SUMPRODUCT? IF? 2
Excel Sumproduct 0
Sumif weekday 3
sumproduct & average 5
formula for adding cells in different rows and different columns 4
How do I count the number of cells that meet multiple criteria? 7
countig of nonblank cells with speciffic condition 3

Top