| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF? Or other?
Excel2003 ...
Range A4:A50 ... contains Text (may repeat) Range D4:J50 .... contains numbers Range A52:A57 ... contains "unique" Text from Range A4:A50 I want to sum all the values in Range D4:J50 when the value in Range A4:A50 matches "unique" value in Range A52:A57. My 1st formula is ... =Sumif(A4:A50,A52,D4:J50) ... but it is not returning value I expect. Col A ..... Col D thru Col J aaaa 1 - 3 - 0 - 3 - 0 - 0 - 0 bbbb 0 - 0 - 0 - 1 - 0 - 0 - 4 aaaa 2 - 1 - 2 - 0 - 1 - 1 - 1 cccc 4 - 2 - 0 - 0 - 2 - 1 - 0 aaaa 3 - 0 - 1 - 1 - 1 - 1 - 2 bbbb 1 - 2 - 1 - 2 - 0 - 2 - 1 Unique ... (return value I wish) aaaa ... = 24 bbbb ... = 14 cccc ... = 9 etc Thanks ... Kha |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF? Or other?
=SUMPRODUCT((A4:A50=A52)*(D4:J50))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message ... Excel2003 ... Range A4:A50 ... contains Text (may repeat) Range D4:J50 .... contains numbers Range A52:A57 ... contains "unique" Text from Range A4:A50 I want to sum all the values in Range D4:J50 when the value in Range A4:A50 matches "unique" value in Range A52:A57. My 1st formula is ... =Sumif(A4:A50,A52,D4:J50) ... but it is not returning value I expect. Col A ..... Col D thru Col J aaaa 1 - 3 - 0 - 3 - 0 - 0 - 0 bbbb 0 - 0 - 0 - 1 - 0 - 0 - 4 aaaa 2 - 1 - 2 - 0 - 1 - 1 - 1 cccc 4 - 2 - 0 - 0 - 2 - 1 - 0 aaaa 3 - 0 - 1 - 1 - 1 - 1 - 2 bbbb 1 - 2 - 1 - 2 - 0 - 2 - 1 Unique ... (return value I wish) aaaa ... = 24 bbbb ... = 14 cccc ... = 9 etc Thanks ... Kha |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF? Or other?
Yes ... worked perfect ... (Once I got rid of a hidden character in 1 of my
cells) Thanks for supporting these boards ... Kha "Bob Phillips" wrote: =SUMPRODUCT((A4:A50=A52)*(D4:J50)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message ... Excel2003 ... Range A4:A50 ... contains Text (may repeat) Range D4:J50 .... contains numbers Range A52:A57 ... contains "unique" Text from Range A4:A50 I want to sum all the values in Range D4:J50 when the value in Range A4:A50 matches "unique" value in Range A52:A57. My 1st formula is ... =Sumif(A4:A50,A52,D4:J50) ... but it is not returning value I expect. Col A ..... Col D thru Col J aaaa 1 - 3 - 0 - 3 - 0 - 0 - 0 bbbb 0 - 0 - 0 - 1 - 0 - 0 - 4 aaaa 2 - 1 - 2 - 0 - 1 - 1 - 1 cccc 4 - 2 - 0 - 0 - 2 - 1 - 0 aaaa 3 - 0 - 1 - 1 - 1 - 1 - 2 bbbb 1 - 2 - 1 - 2 - 0 - 2 - 1 Unique ... (return value I wish) aaaa ... = 24 bbbb ... = 14 cccc ... = 9 etc Thanks ... Kha |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
| Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
| Sumif? | Excel Discussion (Misc queries) | |||
| nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Linear Mode
