COUNTIFS - Multiple criteria in the same column

[cacos]

Hi everyone!

Question: I need to count with several criteria and ranges, only that one of those ranges can include multiple criteria.

For example, in column A:A, the criteria "Jan", "Feb" and "March", and then continue with the rest of ranges and criterias (for example, B:B, "Car models", C:C, "Region", etc).

Also, the criteria that I'd need to lookup in the single column needs to be a cell reference rather than just text.


Thanks!!

 

[Somendra Misra]

Hi,

Consider uploading a sample file.

Regards,

 

[cacos]

Sure! Please check now

 

[Somendra Misra]

Try using:

=SUMPRODUCT(COUNTIF(E3:E4,A3:A19)*COUNTIF(F3:F4,B3:B19)*COUNTIF(G3:G4,C3:C19))

Adjust the criteria range (Red Ref. in formula).

Regards,

 

[Debraj]

@cacos
Your formula will also work, but you have confirm the same using Ctrl + Shift + Enter.

=SUM(COUNTIFS(A3:A19,E3:E4,B3:B19,F3,C3:C19,G3))

BTW, check the attached with another approach as well.
=DCOUNTA(A2:C19,"Month",E2:G4)

 

[ThrottleWorks]

If I am not wrong, using Advanced Filter will also help. Yes, but is not as dynamic as formula.

 

[cacos]

Thanks Debraj and Somendra! And i'll look into DCOUNTA, I've never used database functions.

Thanks ThrottleWorks, you are right but I need a formula approach in this case.