Formula help in a monthly sales report.

Please assist with a SUM function in Excel 2003. I need to add numbers from
4 of 8 columns (sales dollar amounts - the other columns are counts) IF the
text in the row = a certain value (sales agent name). There are over 200
rows (by sales agent) in the worksheet. Then I need to total all of the
sales agents dollars in the report.

SAMPLE: columns seperated by commas to illustrate
Brown, $300, 5,$200, 1
Smith, $250, 3, $100, 1
Brown, $200, 4, $250, 2
Jones, $550, 6, $500, 4
Jones, $600, 4, $450, 5

Formula need to produce:
Brown, $950
Jones, $2100
Smith, $350

chevyman,

Try:

=SUMIF(A2:A6,"Brown",B2:B6)+SUMIF(A2:A6,"Brown",D2:D6)
=SUMIF(A2:A6,"Jones",B2:B6)+SUMIF(A2:A6,"Jones",D2:D6)
=SUMIF(A2:A6,"Smith",B2:B6)+SUMIF(A2:A6,"Smith",D2:D6)

although it would be better if your data was re-arranged so that all the
dollar amounts were in contiguous columns.


HTH

Sandy
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"chevyman" <[email protected]> wrote in message
news:[email protected]...
> Please assist with a SUM function in Excel 2003. I need to add numbers
from
> 4 of 8 columns (sales dollar amounts - the other columns are counts) IF
the
> text in the row = a certain value (sales agent name). There are over 200
> rows (by sales agent) in the worksheet. Then I need to total all of the
> sales agents dollars in the report.
>
> SAMPLE: columns seperated by commas to illustrate
> Brown, $300, 5,$200, 1
> Smith, $250, 3, $100, 1
> Brown, $200, 4, $250, 2
> Jones, $550, 6, $500, 4
> Jones, $600, 4, $450, 5
>
> Formula need to produce:
> Brown, $950
> Jones, $2100
> Smith, $350

Or...
=SUMPRODUCT((A2:A6="Brown")*(B2:B6+D2:D6))

Ola Sandstrom

"Sandy Mann" wrote:

> chevyman,
>
> Try:
>
> =SUMIF(A2:A6,"Brown",B2:B6)+SUMIF(A2:A6,"Brown",D2:D6)
> =SUMIF(A2:A6,"Jones",B2:B6)+SUMIF(A2:A6,"Jones",D2:D6)
> =SUMIF(A2:A6,"Smith",B2:B6)+SUMIF(A2:A6,"Smith",D2:D6)
>
> although it would be better if your data was re-arranged so that all the
> dollar amounts were in contiguous columns.
>
>
> HTH
>
> Sandy
> --
> to e-mail direct replace @mailinator.com with @tiscali.co.uk
>
>
> "chevyman" <[email protected]> wrote in message
> news:[email protected]...
> > Please assist with a SUM function in Excel 2003. I need to add numbers
> from
> > 4 of 8 columns (sales dollar amounts - the other columns are counts) IF
> the
> > text in the row = a certain value (sales agent name). There are over 200
> > rows (by sales agent) in the worksheet. Then I need to total all of the
> > sales agents dollars in the report.
> >
> > SAMPLE: columns seperated by commas to illustrate
> > Brown, $300, 5,$200, 1
> > Smith, $250, 3, $100, 1
> > Brown, $200, 4, $250, 2
> > Jones, $550, 6, $500, 4
> > Jones, $600, 4, $450, 5
> >
> > Formula need to produce:
> > Brown, $950
> > Jones, $2100
> > Smith, $350
>
>
> THANK YOU!!

"Ola" wrote:

> Or...
> =SUMPRODUCT((A2:A6="Brown")*(B2:B6+D2:D6))
>
> Ola Sandstrom
> THANK YOU!