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

try
=sumproduct((rngb={1,2,3})*rngA)

--
Don Guillett
SalesAid Software
[email protected]
"Dale" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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)
>
>

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 <at>yahoo<dot>com
----
"Dale" <[email protected]> wrote in message
news:[email protected]...
> 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)

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 <at>yahoo<dot>com
> ----
> "Dale" <[email protected]> wrote in message
> news:[email protected]...
>
>>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)
>>>
>
>

Glad to hear that !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Dale" <[email protected]> wrote in message
news:[email protected]...
> Thanks Don and Max
>
> Your solutions worked perfectly after I adapted the range. I
> misinterpreted the initial formula that Don gave me. Thanks again

Sorry about the confusion. I often name ranges

--
Don Guillett
SalesAid Software
[email protected]
"Dale" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
> > ----
> > "Dale" <[email protected]> wrote in message
> > news:[email protected]...
> >
> >>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)
> >>>
> >
> >
>