Define range based on 3 conditions then count unique text

[Nick70]

Hi,

I have a range (B4:E17).
In column B I have client name (e.g. client A, client B, etc.) , in column C I have product name (e.g. Product1, Product2" etc.), in column D I have month (Jan to Dec.), and in column E I have Year (2020, 2021 etc.).

I would like to have a formula (say in cell A2) which gives me the number of "unique" products which appear in column C based on 3 conditions: products have been bought by ClientA (in column B), bought in month of February(in column D), bought in year 2021(in column E).

When I say 'unique' products it means that if based on 3 conditions (clientA, February and 2021) we have these products in column C: Product1,Product1,Product2,Product3,Product3 then the formula should give back 3 (i.e. Product1,Product2,Product3).

Which formula or which method should I use?

Thanks,
Nic

 

[Fluff]

How about

=ROWS(UNIQUE(FILTER(C4:C17,(B4:B17="client a")*(D4:D17="February")*(E4:E17=2021))))

 

[Nick70]

Hi,

When I use this formula I get error message #CALC!

Any idea why?

 

[Fluff]

That suggests you don't have any data that matches all the criteria.

 

[Nick70]

You are right!!

I did not check that...

All good then thanks!
Nic

 

[Fluff]

You're welcome & thanks for the feedback.