Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with a Formula
I have a worksheet with two columns of data, (Earnings, and Category). I
need help in using the SUMIF function or SUMPRODUCT to calculate the total from column A for a group of specific catergories. (i.e. category 1 or 2 or 3). I need to be able to total the earnings for all records that are in category 1, 2 or 3 and not in catergory 4 or 6. Using the below example, I would expect the result to be $48. Column B Column C Earnings Category $10 1 $11 2 $12 1 $15 3 $9 4 $10 6 Thanks for the help |
#3
|
|||
|
|||
Thanks for the quick response, however, I must be doing something wrong,
I keep getting the error #Name? Any Ideas? Don Guillett wrote: try =sumproduct((rngb={1,2,3})*rngA) |
#4
|
|||
|
|||
What Don Guillett gave should work ..
=sumproduct((rngb={1,2,3})*rngA) I keep getting the error #Name? Think you didn't adapt "rngb" and "rngA" in Don's formula to suit .. Maybe try this translation: Put in say, D2: =SUMPRODUCT((C2:C100={1,2,3})*B2:B100) Adapt the ranges: C2:C100, B2:B100 to suit -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Dale" wrote in message ... Thanks for the quick response, however, I must be doing something wrong, I keep getting the error #Name? Any Ideas? Don Guillett wrote: try =sumproduct((rngb={1,2,3})*rngA) |
#5
|
|||
|
|||
Thanks Don and Max
Your solutions worked perfectly after I adapted the range. I misinterpreted the initial formula that Don gave me. Thanks again Max wrote: What Don Guillett gave should work .. =sumproduct((rngb={1,2,3})*rngA) I keep getting the error #Name? Think you didn't adapt "rngb" and "rngA" in Don's formula to suit .. Maybe try this translation: Put in say, D2: =SUMPRODUCT((C2:C100={1,2,3})*B2:B100) Adapt the ranges: C2:C100, B2:B100 to suit -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Dale" wrote in message ... Thanks for the quick response, however, I must be doing something wrong, I keep getting the error #Name? Any Ideas? Don Guillett wrote: try =sumproduct((rngb={1,2,3})*rngA) |
#6
|
|||
|
|||
Glad to hear that !
Thanks for the feedback -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Dale" wrote in message ... Thanks Don and Max Your solutions worked perfectly after I adapted the range. I misinterpreted the initial formula that Don gave me. Thanks again |
#7
|
|||
|
|||
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |