Formula help in a monthly sales report.

[Guest]

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

 

[Sandy Mann]

chevyman,

Try:

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

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


HTH

Sandy

 

[Guest]

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

Ola Sandstrom

 

[Guest]

chevyman,

Try:

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

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





THANK YOU!!

 

[Guest]

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

Ola Sandstrom
THANK YOU!