I am trying to count how many times a name appears on 5 different rows (3, 12, 21, 30, 39) on a separate worksheet from where I want the actual count to appear. I've tried COUNTIF, COUNTIFA, and SUM functions and nothing seems to be working. What kind of formula do I need for this?
Post a workbook for experimentation.
Ben Van Johnson
Assuming that Column A contains the names, maybe...
or
Confirmed with CONTROL+SHIFT+ENTER: =SUM(IF(MOD(ROW(A3:A39)-ROW(A3),9)=0,IF(A3:A39="Name",1)))
Note, however, INDIRECT is a volatile function.Confirmed with just ENTER: =SUM(COUNTIF(INDIRECT({"A3","A12","A21","A30","A39"}),"Name"))
Maybe you missed my earlier post. In any case, as per your last post, try...
orD27, confirmed with CONTROL+SHIFT+ENTER: =SUM(IF(MOD(ROW('W2'!$A$5:$A$41)-ROW('W2'!$A$5),9)=0,IF('W2'!$A$5:$A$41='W1'!A27,1)))
D27, confirmed with just ENTER: =SUM(COUNTIF(INDIRECT({"'W2'!A5","'W2'!A14","'W2'!A23","'W2'!A32","'W2'!A41"}),'W1'!A27))
Of course, if the name of interest cannot occur between the target rows, the following would suffice...
=COUNTIF('W2'!$A$5:$A$41,'W1'!A27)
None of those seem to be working for me. I've tried all 3, as well as the 2 in your earlier post. I'm not entirely sure what is meant by "confirm with Control-Shift-Enter", but I think you just press those 3 buttons down to confirm the formula rather than simply Enter? So far all the formulas just come up with a value of 0, when I know they should be coming up with a value of 2.
Am I doing something wrong? Or is there a different way I should be doing it?
EDIT: I lied. Got it working now. I realized that I didn't only have the value I was looking for in the cells I wanted to look for it, so I just made them their own cells. That's what you get for being a novice Excel user. Thanks a ton!
Last edited by juicestain09; 05-05-2012 at 12:22 PM.
And how exactly do I go about doing that? Sorry I'm new to the forums and to Excel.
I'll try to explain everything as best I can:
On the first worksheet, called W1, the name I am using for this is in A27, lets name it John. I would like the result of this formula to be input in D27 as just a count of how many times the name appears. The rows I want to count the name in are on a separate worksheet, called W2. They are rows 5, 14, 23, 32, 41.
If you need any other info, let me know.
You're very welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks