TO ROB and anyone=IF(LOWER(A1)="john",B1,"")

  • Thread starter Thread starter mary
  • Start date Start date
M

mary

First let me say thanks to Rob. This formula works great,
except i need to add all the entries AFTER THIS FORMULA
HAS COLLETED THESE ENTRIES. EXAMPLE: ENTRIES OF
all "john". When i attempt to sum all the entries i am
getting an error message. Basically, because i am adding
up formula cells. Is there a macro that call sum up all
the value of a formula cells.
Thanks again ROB AND ANYONE THAT CAN HELP.
 
Not exactly sure what you have here, but a "=sumif
(Range,"john",Range), might do the trick.
 
There is no problem in summing cells that have formulas in them, but I'll bet
that you are summing them in this manner:-

=C1+C2+C3+C4+C5+C6+C7.............

This will not work because of the "" being returned by some of the formulas.

Change your formula to =SUM(C1:C100) or whatever the ranges are and it will
work.

You can use Davids reply though, in that you don't actually need your 'helper'
column with all your formulas. You can do all that and SUM the results with a
single formula in a single cell, eg:-

=SUMIF(A1:A100,"John",B1:B100)
 
I am using the =SUM(C1:C100)
-----Original Message-----
There is no problem in summing cells that have formulas in them, but I'll bet
that you are summing them in this manner:-

=C1+C2+C3+C4+C5+C6+C7.............

This will not work because of the "" being returned by some of the formulas.

Change your formula to =SUM(C1:C100) or whatever the ranges are and it will
work.

You can use Davids reply though, in that you don't actually need your 'helper'
column with all your formulas. You can do all that and SUM the results with a
single formula in a single cell, eg:-

=SUMIF(A1:A100,"John",B1:B100)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------- -------------------
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------- -------------------






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004


.
Click to expand...
 
Do you have any cells in that range with an error in them, eg #VALUE, #N/A, #NUM
etc

Feel free to mail me a copy of the workbook, or even a copy of just that range
if you prefer and I'll happily take a quick look. You would need to take the
NOSPAM bit out of my email address.
 
Back
Top