excel get count of distinct names where column C is blank

I have a a spreadsheet and I  trying to get a few counts ( need formulas)
1.count of distinct names in column A where the value in column C is blank
2. count of distinct names in column A where the vale of B is 2 and the value of column C is blank

I then need a formula that will allow me to have values of the items it counted in another sheet  ( not the count of 1 & 2, a sheet for 1 and a sheet for 2)

Possible?
neoptoentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
Assuming you have data in rows 2 to 100 you can use these formulas:

=SUM(IF(FREQUENCY(IF(C2:C100="",IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0))),ROW(A2:A100)-ROW(A2)+1),1))

and

=SUM(IF(FREQUENCY(IF(C2:C100="",IF(B2:B100=2,IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0)))),ROW(A2:A100)-ROW(A2)+1),1))

Then to list those in a separate sheet you can use this formula for the first

=IFERROR(INDEX(Sheet1!A$2:A$100,MATCH(1,(Sheet1!C$2:C$100="")*(COUNTIF(A$1:A1,Sheet1!A$2:A$100)=0),0)),"")

and similar for the second

All formulas are "array formulas" so need to be confirmed with CTRL+SHIFT+ENTER in the first cell - if done correctly you will see curly braces like { and } around the formula - and then copy down the column

See attached

Clearly, once you have listed the names by criteria you could simply count the names on that list rather than using the first formulas I suggested, e.g. this formula to count non-blank entries

=COUNTIF(Sheet2!A2:A100,"?*")

regards, barry
distinct-count.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
neoptoentAuthor Commented:
Hi

Thanks

Instead of using a1:a100 I am using the defined name of the sheet.
Team[name]

How can I edit this formula for that.?
barry houdiniCommented:
You can replace most of the references with a named range or structured reference, but in some of my formulas there are references to the first cell in the range and/or an increasing range, which would still need to referenced as a normal cell reference - see revised version attached

If you can't make that work then can you post your data or a sample of it?

regards, barry
distinct-count2.xlsx
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

neoptoentAuthor Commented:
Hi

Is it possible to post the formula in the thread, I can't open the docs
neoptoentAuthor Commented:
Is it possible to add an "or" statement in there.

If row b equals 2 or row b equals 3?
Steve KnightIT ConsultancyCommented:
Unless I am reading it wrong it sounds like an easy way would be a pivot table for Sheet1 and Sheet2?

Have attached an example
EE-Q28690044.xlsx
Pravin AsarPrincipal Systems EngineerCommented:
This is in response post

Is it possible to add an "or" statement in there.

If row b equals 2 or row b equals 3?

Try following simple equation


=SUM(COUNTIFS(B1:B99,{"2","3"},C1:C99,""))

This gives count of rows where b is either 2 or 3 and   c is blank
Pravin AsarPrincipal Systems EngineerCommented:
Applied to your question,  following could work.
This will

count of distinct names in column A where  (B is 2 or 3) and  (C ="")


=SUM(IF(FREQUENCY(IF(C2:C100="",IF(B2:B100={2,3},IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0)))),ROW(A2:A100)-ROW(A2)+1),1))
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.