Count unique distinct values that meet multiple criteria

[Shifter]

Hi,

This is the first time I post for help at an Excel forum, I have been trying to get this to work but none of my ideas worked.

I need to get a formula that gives me the number of clients that have a total greater than zero, from a type of market "national" in a specific month "june"

I cant use pivot tables as I need the formula to use it in different sheets.

Thanks a lot in advance!!


A B C D
ID Client Total Type Month
453 245 National June
453 0 National June
453 434 International June
454 435 International July
454 879 National July
455 0 National June
455 0 International July
457 668 National June
458 464 National July
459 0 International June
460 356 National July

 

[benmcclave]

Hello,

Try this out. It must be entered as an array formula (CTRL+Shift+Enter).

=SUM(IF(B2:B12>0, IF(C2:C12="National",IF(D212="June", 1/(COUNTIFS(B2:B12, ">0", C2:C12, "National", D212, "June",A2:A12, A2:A12))))))