Sumifs with error value in the sum range

[stylissimo]

if this is my spreadsheet:

A B C
1 Item 4 bags #n/a

2 Gross Total------------------> #n/a

3 Item 3 Cosmetics 1985.00

4 Item 3 Cosmetics 1500.00

5 Gross Total------------------> 3485.00

6 GRAND TOTAL--------------> "XXXXXXXXXX" <----this is the part that i
want to know. HOw do i use SUMIF to get the grand total of all gross total
automatically (leaving the #n/a values)

 

[T. Valko]

Try this:

=SUMIF(C1:C5,"<1E100")

 

[stylissimo]

thanks very much! it worked! =D

Try this:

=SUMIF(C1:C5,"<1E100")

 

[stylissimo]

oh one thing, though the formula is working but i just have one concern, if i
will use this range "C1:C5", it will add all valid values under this column,
including the prices of each items, right? I just want to add the values
under "Gross Total" only...how will i do that?

 

[T. Valko]

If you want only the "gross totals" added:

Try this array formula** :

=SUM(IF((A1:A5="gross total")*(ISNUMBER(C1:C5)),C1:C5))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[stylissimo]

kindly check the actual file i have:
http://i228.photobucket.com/albums/ee183/o0wearhouse0o/excel_functionproblem.jpg

how will i get the grand total at "H1361"? if i will just use the range
H42:H315, it will also include the individual prices of the items, right?
so, what function should i use so i can only sum the "Gross Amount" with no
error values automatically?

 

[Bob Phillips]

=SUMIF(C1:C5,"<1E100")/2

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[stylissimo]

i TRied the formula given but it highlighted the word "Amount"..i don't
understand why...

please see the screenshot at the bottom right.

http://i228.photobucket.com/albums/ee183/o0wearhouse0o/excel_functionproblem2.jpg

 

[stylissimo]

i can't use this formula because i will be including the prices of each
items...i just want to filter the valid values on column C under "Gross
Total" and sum it up to get the grand total of it automatically

 

[Bob Phillips]

Yes, but the sum will get double the value, the sum of the individual items,
and the sum of the totals. So just divide by 2 as I showed.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[T. Valko]

i TRied the formula given but it highlighted the word "Amount"..

i don't understand why...

The formula in your screencap *isn't* the same formula I suggested.

First off, use the = sign to start a formula. Get out of the habit of using
the + sign.

This is an *array formula*. An array formula *must* be entered using the key
combination of CTRL, SHIFT, ENTER. Normally when you type a formula you hit
the ENTER key. With an array formula it's different. You use a combination
of keys. Those keys are CTRL, SHIFT, ENTER. That means hold down both the
CTRL key and the SHIFT key then hit ENTER. When done properly Excel will
enclose the formula in squiggly braces { }. You *can not* just type these
braces in. You *must* use the key combination to produce them. Also, anytime
you edit an array formula it *must* be re-entered using the key combination.

Another thing in your screencap, the amounts of "gross total" in column H
have "Php" along side of the numeric value. Is this "Php" a currency format?
If it's a TEXT prefix then you'll need a different formula.

Make sure you use the * in the formula. This is the multiplication operator.
In your screencap you replaced this with a comma. Enter this formula
*exactly* as shown and make sure you use the key combination CTRL, SHIFT,
ENTER.

=SUM(IF((F12:F1382="gross total")*(ISNUMBER(H12:H1382)),H12:H1382))