Count number of non blank cells based on another colomn unique values

1
votes

I have been trying to count the non-blank entries of a colomn based on another colomn's value, problem is there is multiple instances. Please see the following table 

F.No    Name                    2014-2015
1       Mithilesh Kr.Singh      
1       Mithilesh Kr.Singh      
1       Mithilesh Kr.Singh      
1       Mithilesh Kr.Singh      
1       Mithilesh Kr.Singh      
1       Mithilesh Kr.Singh      
1       Mithilesh Kr.Singh    3434
1       Mithilesh Kr.Singh    4234
1       Mithilesh Kr.Singh      
2       Jaiprakash Singh            
2       Jaiprakash Singh      234
2       Jaiprakash Singh        
2       Jaiprakash Singh      342
2       Jaiprakash Singh            
2       Jaiprakash Singh            
2       Jaiprakash Singh      3423
3       Narayan Jha                 
3       Narayan Jha                 
3       Narayan Jha

As you can see there are repeated names, I have to count that how many unfilled cells are there for colomn 1's unique value, for example for F.No 1 the count for year 2014-15 must be 2, likewise for F.No 2 the count must be 3 and for F.No 3 the count must be 0.

Is there any CountA or Countif formulas that could help me ? PS: Colomn 2 is just for the sake of relevance to the situation.

Edit 1: I made some progress , =COUNTIFS(A:A,"3",C:C,"<>") , this gives me count for F.No = 3, but I don't want to enter 1,2,3 and so on F.Nos, What should I replace with 3 in above formula?

Edit 2: Some more progress ( thanks to @Shauno_88 ) =IF(COUNTIFS(A:A,"1",C:C,"<>")>0,1,0) This is returning me 1 if there is any single entry for F.NO 1 in column A, Now How to get the count for all other F.Nos ?? Any Macros ? Any VBA ?

2
excelexcel-formula
Is this a once off process? If so you could just create a pivot table and use the count function for one of your columns. - Shauno_88
@Shauno_88 I am not sur I understood by what once off means, this table is expanding continiously but the formula should work. Here is what I acheived so far =COUNTIFS(A:A,"3",C:C,"<>") where I need to put 1, 2 ,3 etc F.No values to get the count , I don't want that, I want this to auto calculate based of unique F.No (colomn 1)'s value - Sushant Choudhary

2 Answers

1
votes

This will help you do the first part that you are after. But I'm not sure how you will do the total.

=IF(COUNTIFS(A:A,A2,C:C,"<>")>0,1,0)
0
votes

this will work 

=COUNTIFS(D:D,F1,E:E,"")

Assuming the list of names above is in column D F1 is the cell containing 1 instance of the name E is the column your checking for blanks