ExcelBanter - Excel adding a row - follow up to replies

One guess at your new issue
can now add one row up, ie the Ys
but can't seem to get a formula right to ask for 3 codes to add up
(OC, N, NC)

Shane gave you this earlier:
=COUNTIF(B1:Z1,"Y")

1. Maybe you meant to do this:
=SUM(COUNTIF(B1:Z1,{"OC","N","NC"}))
which will count all occurences of: OC or N or NC
in the range B1:Z1

2. If your data in cols B to Z may contain "invisible" white spaces caused
by inconsistent data entry or whatever, you could use TRIM (in SUMPRODUCT*)
for improved robustness to return the count, eg below:
*as COUNTIF will not accept TRIM

=SUMPRODUCT(--(TRIM(B1:Z1)="Y"))
Equiv. to: =COUNTIF(B1:Z1,"Y")

=SUMPRODUCT(--ISNUMBER(MATCH(TRIM(B1:Z1),{"OC","N","NC"},0)))
Equiv. to: =SUM(COUNTIF(B1:Z1,{"OC","N","NC"}))

P/s: Suggest that you always treat each new query that you put in, in its
own right. Describe it *completely* in your post, paste the exact formula you
are using, where it is, what problem you face, what you want to happen, where
is the output cell to be etc. Not many responders will have the time nor
energy to hunt down your previous thread.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Linda" wrote:
Thanks very much for your messages Shane and Don. I can now add one row up,
ie the Ys but can't seem to get a formula right to ask for 3 codes to add up
(OC, N, NC) even though I have a mound of printed help info around me! May
give up for tonight but if you know.... I would welcome your help again.
Thanks. (Its the first time I've written in but like to look through queries
and have learnt info - mainly that I know a lot less than I thought I did!)