Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Or...
=SUMPRODUCT((A2:A6="Brown")*(B2:B6+D2:D6)) Ola Sandstrom |
#4
|
|||
|
|||
"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!! |
#5
|
|||
|
|||
"Ola" wrote: Or... =SUMPRODUCT((A2:A6="Brown")*(B2:B6+D2:D6)) Ola Sandstrom THANK YOU! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto fill of formula | Excel Worksheet Functions | |||
formula to calculate sales tax from total sales | Excel Worksheet Functions | |||
EXCEL WOULD LIKE TO KNOW HOW TO LINK INVOICES TO A SALES REPORT | Excel Discussion (Misc queries) | |||
looking for a formula | Excel Worksheet Functions | |||
What formula can you use to compound monthly returns? | Excel Worksheet Functions |