Excell-How to add the number of cells containing text? = a sum

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aruba64taw - 21 Dec 2005 06:27 GMT

For a wedding. I have the different meals in seperate columns. I need to add
the number of Beef dinners and the number of Chicken dinners. I cannot use a
number conveniently for the "Beef" or the "Chicken". I tried
=IF(G95="Beef","1","0") with the conditional number "1" going into a side
work column and then summed that work column. But apparently the "1" in the
conditional statement is not a 'number' 1. As soon as I substituted a number
1 for the conditional 1, the sum worked.

Reply to this Message

Biff - 21 Dec 2005 07:12 GMT

Hi!

>=IF(G95="Beef","1","0")

That formula is returning TEXT and not the numeric numbers you think it is.
That's why when you tried a sum of that column it didn't work.

It should be written like this:

=IF(G95="Beef",1,0)

Another way to do what you want and not use a whole column IF formulas:

A1 = Beef
A2 = Chicken

B1 = formula:

=COUNTIF(G$1:G$100,A1)

Copy down into cell B2.

Biff

> For a wedding. I have the different meals in seperate columns. I need to
> add
[quoted text clipped - 7 lines]
> number
> 1 for the conditional 1, the sum worked.

Reply to this Message

Bob Phillips - 21 Dec 2005 09:51 GMT

Notwithstanding Biff's answer, you should have used numbers in the formula

=IF(G95="Beef",1,0)

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> For a wedding. I have the different meals in seperate columns. I need to add
> the number of Beef dinners and the number of Chicken dinners. I cannot use a
[quoted text clipped - 3 lines]
> conditional statement is not a 'number' 1. As soon as I substituted a number
> 1 for the conditional 1, the sum worked.

Reply to this Message