Count only 315 and higher? Count shaded cells?

[Pheasant Plucker®]

Hi there,

I wonder if some kind soul can help me on these two issues please?

I am currently using the formula =COUNT(P221) to count the number of
entries in a column.

However I now have a need to only count those cells that contain the cost
315 and higher - i.e. ignoring anything under 315

How do I do this?

Is it simply a case of altering my formula to read =COUNT(P221 <315) or is
there a better/proper way to do it?


On a separate issue is there a way to total up the cost of only those cells
that are shaded - i.e.. when I reconcile an invoice I shade the box grey to
distinguish it from data that has not been invoiced but for which I know the
cost?

Currently I use the standard =SUM(P221) to calculate the column total but
I would like to add another calculation that only totals those cells that I
have reconciled (shaded)

And the opposite formula to only count those cells unshaded with no colour?

Is this even possible?

 

[Andy B]

PP

Have a look at COUNTIF(). On the question of shaded cells, visit Chip's
site. There's lots of useful stuff that can help you:
http://www.cpearson.com/excel/colors.htm

Andy.

 

[Paul]

There is a much easier alternative to summing shaded cells if you are
willing to change the way you mark reconciled cells. Suppose instead of
shading cells in column P you were to put a character # (for example) in the
adjacent column Q. Simple formulas will then sum reconciled/unreconciled
invoice amounts:
=SUMPRODUCT((Q2:Q21="#")*P221)
=SUMPRODUCT((Q2:Q21<>"#")*P221)

 

[Pheasant Plucker®]

Thanks for the info guys...used COUNTIF(P221,">314") and it seems to work!


Had a look at the link given for identifying the shaded boxes but afraid
most of it is over my head...:-(

Thanks & regards,
-=Glyn=-