"not count" certain cells in a SUM function

[lindasf]

Hi,

I have a spreadsheet (attached) that our users use to input numbers
such as those in cells BR14:BR19).

As you can see, a subtotal is displayed in BR22.

Is there a way to do a sum in cell BR41 without manually subtractin
out BR22?

For example, could I "Not count" BR22 if it were RED or BOLD?

I am open to other options.

Cell CW41 is particularly onerous as I must subtract out 2 cells. Thx
much.

lindas

Attachment filename: training schedule by programs-short.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=65760

 

[guilbj2]

Could you expand on the reason that you're looking to exclude BR22 ? I
it's a certain value, you could exclude it using "IF" and simply us
multiple sum ranges to get a total. For instance, if you wanted t
exclude BR22 if the value was "5"...

=If(br22=5,sum(br1:br21)+sum(br23:br"whatever"),sum(br1:br"whatever")

That formula will run the sum for b1 to whatever if the value is not 5
but exclude br22 if it IS = 5. I don't know if it's possible to set a
IF condition based on formatting (bold etc.) but I don't believe it is


If you can give a little more info on why you're looking to exclude it
I might be able to come up with something different for you

 

[David McRitchie]

Hi Linda,
If you had actually used SUBTOTAL instead of SUM it would not
include other SUBTOTALs. You have faked the data and totals
so it is hard to tell from your example exactly what you want, and
that is part of the reason
why people generally don't look at the "attachments", because the
problem is not clearly stated. Yours is really a dataset not an
attachment which is a lot better than an actual attachment.

But clearly written questions are preferred because they can be
done entirely in text, and the entire question and answer can be
seen in Google Groups when others search for answers.

Look up SUBTOTAL(9, in your Worksheet HELP.
An example of hidden rows with SUBTOTAL in
http://www.mvps.org/dmcritchie/excel/sumdata.htm#subtotals
which is not part of you question but is interesting.

That said I don't really see the problem with using SUM to address
specific rows. =SUM(range1, range2, range3)

If you want the ability to add more grocery stores as rows you might
want to use OFFSET in you totals see
http://www.mvps.org/dmcritchie/excel/insertrow.htm#offset

Don't forget you can use the Status Bar SUM to check
some of your work.
http://www.mvps.org/dmcritchie/excel/statusbar.htm

 

[lindasf]

Guilbj2 - Thx. for responding. I'm guessing that you looked at m
spreadsheet.

Unfortunately I think you answered my question when you said:

“I don't know if it's possible to set an IF condition based o
formatting (bold etc.) but I don't believe it is.”

I inherited this spreadsheet and the users use it to input values i
certain cells (such as BR14:BR19). They can then view the subtotal i
BR22.

When I do a grand total at the bottom, I want to count either BR14:BR1
OR BR22 - but not both because then I would be counting duplicat
values.

I already modified my bottom SUMs (grand totals) to not count th
duplicate values but it's an inelegant solution. It would be great i
there were a simple way to not count values based on cell formatting.

I'm just trying to avoid too much manual work. In addition, all the SU
formulas on row 41 are different, which is klugey. (However I can liv
with it if have too ...)

Thx. again.

lindas

 

[guilbj2]

My traditional solution with inherited spreadsheets that generate to
much manual work is to do all of the manual work at once and be don
with it.... re-design it from scratch to work properly..

 

[lindasf]

guilbj2 - I agree with you.

Actually, I've already modified the spreadsheet to work as needed. It
really didn't take that long - it's just klugey and I HATE THAT!
.

The bad news is that I must do this to other (similar) spreadsheets.

The good news is that this will eventually be in a database ... but
that's another story ... Thx.

lindasf

 

[guilbj2]

I'm just learning Access myself. Lots of benefits to the databases, bu
the learning curve is a lot steeper than Excel.

I hope everything is less "klugey" in the future