Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells formatted as currency, need to sum just positives...
Hi, hope someone can help..
I have columns on monetary values arrived at by: Row A Quantity (fed from another sheet) Row B Equals row A Row C Equals Row B multiplied by a unit cost in another cell. This spreadsheet is used for inventory tracking, and I need to sum up the totals (Row B) only if they are positive numbers, negative numbers need to be treated as zeros. Rows A,B and C are duplicated down my spreadsheet for different items (engines actually) and need to totalled only if positive. I hope this makes sense, can I explain any easier if not? Thanks in advance. Mark (email: ) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells formatted as currency, need to sum just positives...
=SUM(IF(B2:B200),B2:B20))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... Hi, hope someone can help.. I have columns on monetary values arrived at by: Row A Quantity (fed from another sheet) Row B Equals row A Row C Equals Row B multiplied by a unit cost in another cell. This spreadsheet is used for inventory tracking, and I need to sum up the totals (Row B) only if they are positive numbers, negative numbers need to be treated as zeros. Rows A,B and C are duplicated down my spreadsheet for different items (engines actually) and need to totalled only if positive. I hope this makes sense, can I explain any easier if not? Thanks in advance. Mark (email: ) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells formatted as currency, need to sum just positives...
Try this:
=SUMIF(B1:B100,"0") -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === wrote in message ups.com... Hi, hope someone can help.. I have columns on monetary values arrived at by: Row A Quantity (fed from another sheet) Row B Equals row A Row C Equals Row B multiplied by a unit cost in another cell. This spreadsheet is used for inventory tracking, and I need to sum up the totals (Row B) only if they are positive numbers, negative numbers need to be treated as zeros. Rows A,B and C are duplicated down my spreadsheet for different items (engines actually) and need to totalled only if positive. I hope this makes sense, can I explain any easier if not? Thanks in advance. Mark (email: ) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells formatted as currency, need to sum just positives...
Thank you for the replies, I have used SUMPRODUCT and it satisfies my needs 100%. Thank you for taking the time to reply. Regards, Mark Ford Motor Co. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I concatenate two currency cells to show a price range? | Excel Worksheet Functions | |||
how do i concatenate conditionally formatted cells in excel | Excel Worksheet Functions | |||
copy formatted (red font) cells from one worksheet to another | Excel Discussion (Misc queries) | |||
Subtracting Cells formatted with Date AND Time to get hours? | Excel Discussion (Misc queries) | |||
Adding formatted cells to an outline ? | Excel Discussion (Misc queries) |