count number and blanks and auto filter

[gondal]

hi all

this is only sample data..

in attached list i want that in column G and E total count of numbers and total blanks of each against name and when i click on one blank like H2 it shows all blanks of Ahsan zia auto and similar when i click on number (G2) that show all numbers and similarly for all..

Gondal

[AliGW]

Please re-post your file with some expected outcomes filled in manually. What do you want in the totals column? A count or a sum?

[AliGW]

Perhaps this in H2 copied down:

=COUNTIFS($B$2:$B$1001,F2,$C$2:$C$1001,"")

and this in G2 copied down:

=COUNTIFS($B$2:$B$1001,F2,$C$2:$C$1001,"<>")

[gondal]

PFA for your reference but most important that auto filter for me..like clike on cell H2 all blanks of Ahsan zia (F2) show and ssme for G2.

[AliGW]

Please explain the numbers in column G - how have you calculated them?

As I stated above, this is the formula for column H:

=COUNTIFS($B$2:$B$1001,F2,$C$2:$C$1001,"")

[gondal]

these are count which exist in column C except blanks, result of column H is ccorrect,

[gondal]

make filter in column B and check on ahsan zia than make filter in column C and unchecked blanks total count will be 153..and result i need like this

[AliGW]

Try this in G:

=COUNTIFS($B$2:$B$1001,F2,$C$2:$C$1001,">0")

[gondal]

thanks ali, its seems good now but also need to count total blanks (blank in B)in column B and c and auto filter,,??can you make for me

[AliGW]

Just create your summary table above the main table so that it remains visible when you apply filters to the cells below. Insert enough rows above to accommodate it, then move it there. You could also freeze the cells above the main table. I am sure that you can manage this yourself!

[gondal]

may you are right Ali but after making summery i have to sent this summery to all person which are showing in F..i will shift the table in sheet 2,auto filter necessary for me..if possible to make

[AliGW]

may you are right Ali but after making summery i have to sent this summery to all person which are showing in F..i will shift the table in sheet 2,auto filter necessary for me..if possible to make

I don't understand what you are now saying - sorry. Can you illustrate what you mean?

[gondal]

dear ali

there is 163 number (count) are there in column C but there is no data in column B. need also adjust in formula in cell G13 as 163..

[AliGW]

No, I'm sorry - I am completely lost. Attach another worksheet with the layout exactly as you want it and some expected results populated so that we can see what you mean.

[gondal]

please yellow highlighted figure in attached

[AliGW]

This isn't really too hard:

=COUNTIF($B$2:$B$1001,"")

May I suggest that you do a bit of reading up in the Help file on the COUNTIF and COUNTIFS functions?

[gondal]

thanks ali..thnx for helping me..definitely i will get idea of as you suggested

[AliGW]

Glad to help. You will learn more now by trying to adapt what you have been given rather than asking us to do it for you for each new scenario.

Please mark the thread as solved.