Unable to sum the specific cells containing both text & values

[Shafaq Shabieh]

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

 

[Bernard Liengme]

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

 

[Ashish Mathur]

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

 

[RagDyeR]

Try this:

=SUMIF(A1:E10,"<"&99^99)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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 Shabieh]

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 are: 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 Shabieh]

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!