Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to sum the specific cells containing both text & values
In an excel sheet, i have gathered the data for sales, redemption and Net
sales of all the mutual funds for the period of October. The excell sheet depicts the daily sales, redemption & Net sales of each fund. So there are 27 columns for sales numbers, 27 for redemptions, and 27 for Net sales (excluding sundays from the month). most of the cells contain numbers while some cells do contain "N/A" in order to highlight that there was no sale on that specific day due to book closure. now i want to add the total sales, redemptions and net sales of each fund for the whole month. i can not add the numbers by supplying range, i have to click specific cells in order to get the grand total. The format below shows how values are entered into the excel: A B C D E F G X Y Z FUND OCT 4 Oct 6 ...... Oct 31 Total (Oct) Name Sales Red Net-Sal Sales Red Net-Sal Sal Red Net-Sal Sales Red Net-Sal ABC 23 45 -22 N/A 35 12 23 (A, B, C, D, E .... Z = COLUMNS) please, let me know if there is any way i can sum the total sales/redemptions/net sales. i have Ms Excel 2007, Vista. Thank you -- Shafaq |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to sum the specific cells containing both text & values
To sum the numbers in A10:G10 when some of the cells could have text or
error codes such as #N/A, use =SUM(IF(ISNUMBER(A10:G10),A10:G10,0)) but since this is an array formula, commit it with SHIFT+CTRL+ENTER not just ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Shafaq Shabieh" wrote in message ... In an excel sheet, i have gathered the data for sales, redemption and Net sales of all the mutual funds for the period of October. The excell sheet depicts the daily sales, redemption & Net sales of each fund. So there are 27 columns for sales numbers, 27 for redemptions, and 27 for Net sales (excluding sundays from the month). most of the cells contain numbers while some cells do contain "N/A" in order to highlight that there was no sale on that specific day due to book closure. now i want to add the total sales, redemptions and net sales of each fund for the whole month. i can not add the numbers by supplying range, i have to click specific cells in order to get the grand total. The format below shows how values are entered into the excel: A B C D E F G X Y Z FUND OCT 4 Oct 6 ...... Oct 31 Total (Oct) Name Sales Red Net-Sal Sales Red Net-Sal Sal Red Net-Sal Sales Red Net-Sal ABC 23 45 -22 N/A 35 12 23 (A, B, C, D, E .... Z = COLUMNS) please, let me know if there is any way i can sum the total sales/redemptions/net sales. i have Ms Excel 2007, Vista. Thank you -- Shafaq |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to sum the specific cells containing both text & values
Hi,
From your query I understand that you cannot supply the entire range to sum because of the N/A's, Use this formula for summing =sumif(range,"0")+sumif(range,"<0") This formula will sum up all the numbers -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Shafaq Shabieh" wrote in message ... In an excel sheet, i have gathered the data for sales, redemption and Net sales of all the mutual funds for the period of October. The excell sheet depicts the daily sales, redemption & Net sales of each fund. So there are 27 columns for sales numbers, 27 for redemptions, and 27 for Net sales (excluding sundays from the month). most of the cells contain numbers while some cells do contain "N/A" in order to highlight that there was no sale on that specific day due to book closure. now i want to add the total sales, redemptions and net sales of each fund for the whole month. i can not add the numbers by supplying range, i have to click specific cells in order to get the grand total. The format below shows how values are entered into the excel: A B C D E F G X Y Z FUND OCT 4 Oct 6 ...... Oct 31 Total (Oct) Name Sales Red Net-Sal Sales Red Net-Sal Sal Red Net-Sal Sales Red Net-Sal ABC 23 45 -22 N/A 35 12 23 (A, B, C, D, E .... Z = COLUMNS) please, let me know if there is any way i can sum the total sales/redemptions/net sales. i have Ms Excel 2007, Vista. Thank you -- Shafaq |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to sum the specific cells containing both text & values
Try this:
=SUMIF(A1:E10,"<"&99^99) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Shafaq Shabieh" wrote in message ... In an excel sheet, i have gathered the data for sales, redemption and Net sales of all the mutual funds for the period of October. The excell sheet depicts the daily sales, redemption & Net sales of each fund. So there are 27 columns for sales numbers, 27 for redemptions, and 27 for Net sales (excluding sundays from the month). most of the cells contain numbers while some cells do contain "N/A" in order to highlight that there was no sale on that specific day due to book closure. now i want to add the total sales, redemptions and net sales of each fund for the whole month. i can not add the numbers by supplying range, i have to click specific cells in order to get the grand total. The format below shows how values are entered into the excel: A B C D E F G X Y Z FUND OCT 4 Oct 6 ...... Oct 31 Total (Oct) Name Sales Red Net-Sal Sales Red Net-Sal Sal Red Net-Sal Sales Red Net-Sal ABC 23 45 -22 N/A 35 12 23 (A, B, C, D, E .... Z = COLUMNS) please, let me know if there is any way i can sum the total sales/redemptions/net sales. i have Ms Excel 2007, Vista. Thank you -- Shafaq |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to sum the specific cells containing both text & values
Hi,
Thanks for replying to my query. i just wanted to clarify that i can not supply the entire range to sum because i need 3 different totals for the whole month , that a Total Sales, Total Redemptions & Total Net Sales. Now, the way data has been gathered, it is important to select the cells individually, hence not the range. for more clarification: column# 1 contains sales for Oct 4 column# 2 contains redemptions for Oct 4 column# 3 contains Net sales for Oct 4 column# 4 contains sales for Oct 5 column# 5 contains redemptions for Oct 5 column# 6 contains Net sales for Oct 5 till column# 90 contains sales for Oct 31 column# 91 contains redemptions for Oct 31 column# 92 contains Net sales for Oct 31 Now what i need is: Total Sales for the whole month of Oct Total Redemptions for the whole month of Oct Total Net-Sales for the whole month of Oct Hope, now you must have understood, what exactly i need. i can not supply a range as it will give me a cumulative figure for all 3 things (sales, redemptions & net sales). Secondly, when i supplied a range to sum, the simple function "=sum(A:Z)" gave me the cumulative figure, ignoring the text "N/A", but the issue is i can not supply a range because it adds every thing then. And when i pick the columns containing sales only, then the problem arises as a few cells contain "N/A" i.e. =(column1+ column 4+......+column90) "In this case it does not ignore "N/A" or text" It would be great, if there is any way i could forward you the excel file for your perusal. -- Shafaq "Ashish Mathur" wrote: Hi, From your query I understand that you cannot supply the entire range to sum because of the N/A's, Use this formula for summing =sumif(range,"0")+sumif(range,"<0") This formula will sum up all the numbers -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Shafaq Shabieh" wrote in message ... In an excel sheet, i have gathered the data for sales, redemption and Net sales of all the mutual funds for the period of October. The excell sheet depicts the daily sales, redemption & Net sales of each fund. So there are 27 columns for sales numbers, 27 for redemptions, and 27 for Net sales (excluding sundays from the month). most of the cells contain numbers while some cells do contain "N/A" in order to highlight that there was no sale on that specific day due to book closure. now i want to add the total sales, redemptions and net sales of each fund for the whole month. i can not add the numbers by supplying range, i have to click specific cells in order to get the grand total. The format below shows how values are entered into the excel: A B C D E F G X Y Z FUND OCT 4 Oct 6 ...... Oct 31 Total (Oct) Name Sales Red Net-Sal Sales Red Net-Sal Sal Red Net-Sal Sales Red Net-Sal ABC 23 45 -22 N/A 35 12 23 (A, B, C, D, E .... Z = COLUMNS) please, let me know if there is any way i can sum the total sales/redemptions/net sales. i have Ms Excel 2007, Vista. Thank you -- Shafaq |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to sum the specific cells containing both text & values
Hi,
Thanks for replying to my query. Kindly note that the simple function =sum(A10:G10) produces the same result as the one highlighted by you. Both functions do ignore the text, but the issue is i can not supply a range to sum. i have elaborated every thing in response to Ashish's reply. Plz check that. Thanks once again! -- Shafaq "Bernard Liengme" wrote: To sum the numbers in A10:G10 when some of the cells could have text or error codes such as #N/A, use =SUM(IF(ISNUMBER(A10:G10),A10:G10,0)) but since this is an array formula, commit it with SHIFT+CTRL+ENTER not just ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Shafaq Shabieh" wrote in message ... In an excel sheet, i have gathered the data for sales, redemption and Net sales of all the mutual funds for the period of October. The excell sheet depicts the daily sales, redemption & Net sales of each fund. So there are 27 columns for sales numbers, 27 for redemptions, and 27 for Net sales (excluding sundays from the month). most of the cells contain numbers while some cells do contain "N/A" in order to highlight that there was no sale on that specific day due to book closure. now i want to add the total sales, redemptions and net sales of each fund for the whole month. i can not add the numbers by supplying range, i have to click specific cells in order to get the grand total. The format below shows how values are entered into the excel: A B C D E F G X Y Z FUND OCT 4 Oct 6 ...... Oct 31 Total (Oct) Name Sales Red Net-Sal Sales Red Net-Sal Sal Red Net-Sal Sales Red Net-Sal ABC 23 45 -22 N/A 35 12 23 (A, B, C, D, E .... Z = COLUMNS) please, let me know if there is any way i can sum the total sales/redemptions/net sales. i have Ms Excel 2007, Vista. Thank you -- Shafaq |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
assign values to specific cells in an excel sheet through text file | Excel Discussion (Misc queries) | |||
HELP!!! I am unable to see colored text or highlighted cells ... | Excel Discussion (Misc queries) | |||
Unable to pre-format cells to text when exporting data to csv or x | Excel Discussion (Misc queries) | |||
Summing values in a range against specific text | Excel Discussion (Misc queries) | |||
formatting text on chart for specific values | Charts and Charting in Excel |