find active members

[mwvirk]

how i can find if:
cell A1:A500 has the word 'Active' and cell B1:B500 is blank (note: B already has a VLOOKUP)

I have 500 clients where maybe 200 are active and I want to know out of 200 how many are generating commission for me

sorry I don't have a sample sheet for this at this time.

 

[Asbestos_Jen]

Assuming your lookup returns "" in column B, what do you want to happen when A=active and B=""? What would you return, and where? Or are you looking for a count of rows that meet those criteria?

 

[mwvirk]

Assuming your lookup returns "" in column B, what do you want to happen when A=active and B=""? What would you return, and where? Or are you looking for a count of rows that meet those criteria?

i want to check if client status is 'Active' then in B if it's blank then count how many are there not making any income.

 

[Joe4]

Maybe something like:

=COUNTIFS(A2:A500,"Active",B2:B500,"",C2:C500,"<>")

to count the number where:
- A is Active
- B is blank
- C is NOT blank

 

[mwvirk]

Maybe something like:

=COUNTIFS(A2:A500,"Active",B2:B500,"",C2:C500,"<>")

to count the number where:
- A is Active
- B is blank
- C is NOT blank

thank you.
Blank are calculated perfectly
But NOT blank is showing total clients

 

[Joe4]

What do the formulas in column B and C look like, exactly?
Can you post an example of one of each?

 

[mwvirk]

What do the formulas in column B and C look like, exactly?
Can you post an example of one of each?

B is data validation
C is =IF(ISNA(VLOOKUP(B2,Commission!A:E,4,FALSE)),"",VLOOKUP(B2,Commission!A:E,4,FALSE))

and I am trying to get the count in D

 

[Joe4]

Column D?
Isn't column D already populated?
There seems to be a discrepancy between your initial description, image, and formulas.

What columns are you actually showing us in your original picture?
I don't think you are using column B (the blank Comments column) for your lookups.

 

[mwvirk]

Column D?
Isn't column D already populated?
There seems to be a discrepancy between your initial description, image, and formulas.

What columns are you actually showing us in your original picture?
I don't think you are using column B (the blank Comments column) for your lookups.

I split it into 2 cell and it's working now:
=COUNTIFS(A2:A500,"Active",B2:B500,"")
=COUNTIFS(A2:A500,"Active",B2:B500,">0")

but now I need to change it
rather than only 'Active'
I need multiple checks
if Cell A is Active or Pending or Block or Closed
Is it possible?

 

[Joe4]

One way would be to create a formula for each and add together, i.e.

=COUNTIFS(A2:A500,"Active",B2:B500,"")+COUNTIFS(A2:A500,"Pending",B2:B500,"")+COUNTIFS(A2:A500,"Blocked",B2:B500,"")+COUNTIFS(A2:A500,"Closed",B2:B500,"")