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" wrote in message
...
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" wrote in message
...
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!