Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct/SumIf
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct and sumif | Excel Worksheet Functions | |||
SUMIF OR SUMPRODUCT?? | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
SumIf or SumProduct | Excel Worksheet Functions | |||
SUMPRODUCT or SUMIF | Excel Discussion (Misc queries) |