XL2003. I've checked many of the answers in this forum, but none has worked
for me.
One column in my table has staff initials where the same few people are
scattered down the column. A dozen more columns have events marked with an X.
I want to count the number of Xs for each person. I've named the dozen
columns 'Xarray'.

I've tried variations on COUNTIF($B$2:$B$500="AB",Xarray,"X"), sometimes
CSE-ing them, sometimes using AND() and SUMIF() in different ways, but get
either an error-in-formula message, or the total number of Xs, or the total
number of cells, or FALSE or #N/A.

Please would someone tell me where I'm going wrong?

try this idea
=SUMPRODUCT((C2:C22="a")*(D2:F22="x"))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jonathan589" wrote in message
...
XL2003. I've checked many of the answers in this forum, but none has
worked
for me.
One column in my table has staff initials where the same few people are
scattered down the column. A dozen more columns have events marked with an
X.
I want to count the number of Xs for each person. I've named the dozen
columns 'Xarray'.

I've tried variations on COUNTIF($B$2:$B$500="AB",Xarray,"X"), sometimes
CSE-ing them, sometimes using AND() and SUMIF() in different ways, but get
either an error-in-formula message, or the total number of Xs, or the
total
number of cells, or FALSE or #N/A.

Please would someone tell me where I'm going wrong?

Don, Francis, and ryguy7272: thank you all for your quick and useful
responses: SUMPRODUCT and SUM worked perfectly for me. I think I feel silly
for not having got it right before, but I'm there now!

Hi Jonathan

Thank you for your feedback. Am glad that these have been helpful
Me too was silly and is still learning, Excel can surprises me with
something new everyday. Best wishes.

Would you mind clicking on the Yes button below the posts that have answered
your question, this will help others to find the solution easlier in the
archive
later. Thanks
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Jonathan589" wrote:

Don, Francis, and ryguy7272: thank you all for your quick and useful
responses: SUMPRODUCT and SUM worked perfectly for me. I think I feel silly
for not having got it right before, but I'm there now!

one way, try this array formula, confirm by CSE

=SUM((A2:A500="AB")*(B2:F500="x"))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Jonathan589" wrote:

XL2003. I've checked many of the answers in this forum, but none has worked
for me.
One column in my table has staff initials where the same few people are
scattered down the column. A dozen more columns have events marked with an X.
I want to count the number of Xs for each person. I've named the dozen
columns 'Xarray'.

I've tried variations on COUNTIF($B$2:$B$500="AB",Xarray,"X"), sometimes
CSE-ing them, sometimes using AND() and SUMIF() in different ways, but get
either an error-in-formula message, or the total number of Xs, or the total
number of cells, or FALSE or #N/A.

Please would someone tell me where I'm going wrong?

Couple great tutorials right he
http://www.contextures.com/xlFunctions04.html
http://www.contextures.com/xlFunctions01.html

HTH,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Francis" wrote:

one way, try this array formula, confirm by CSE

=SUM((A2:A500="AB")*(B2:F500="x"))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Jonathan589" wrote:

XL2003. I've checked many of the answers in this forum, but none has worked
for me.
One column in my table has staff initials where the same few people are
scattered down the column. A dozen more columns have events marked with an X.
I want to count the number of Xs for each person. I've named the dozen
columns 'Xarray'.

I've tried variations on COUNTIF($B$2:$B$500="AB",Xarray,"X"), sometimes
CSE-ing them, sometimes using AND() and SUMIF() in different ways, but get
either an error-in-formula message, or the total number of Xs, or the total
number of cells, or FALSE or #N/A.

Please would someone tell me where I'm going wrong?