Using a COUNTIF but need two criteria

claudia.alay

Hello,

I have a master data sheet with all my information and another sheet where I have my KPIs/metrics & formulas. I am currently using a COUNTIF formula

=COUNTIF({Project List Range 1}, Labels@row)

this is counting how many projects I have in each status (new, in process, pending, etc.) I would like to add another criteria to this to only count the line if the 'Project Type' column says external. Am i able to do so and how would I add to my existing formula?

thanks!

Nic Larsen

You just need to change the COUNTIF to COUNTIFS and add your 2nd range and 2nd criteria

=COUNTIFS({Project List Range 1}, Labels@row, Range2, Criteria2

Almonto1

I recommend using the COUNTIFS formula for multiple criteria. Example below!

=COUNTIFS({Project List Range 2}, "External", {Project List Range 1}, Labels@row)

Project list range 2: the project type

Project list range 1: the project status

Nic Larsen

You just need to change the COUNTIF to COUNTIFS and add your 2nd range and 2nd criteria

=COUNTIFS({Project List Range 1}, Labels@row, Range2, Criteria2

Almonto1

I recommend using the COUNTIFS formula for multiple criteria. Example below!

=COUNTIFS({Project List Range 2}, "External", {Project List Range 1}, Labels@row)

Project list range 2: the project type

Project list range 1: the project status

SaraM2245

Hello- I am trying to follow the syntax recommended here but I am still getting an invalid reference.

I am trying to create a summary formula for all active risks in my QRAIDD log. What am I missing here?

Type = Risk and Status=Open

=COUNTIFS({Type:Type}, "Risk", {Status:Status}, "Open")

Thanks for your help

Nic Larsen

If your formula above isn't a cross sheet references, then you need to remove the brackets. If it is Cross Sheet, then also needs edit. Something along the lines of:

In same Sheet

=COUNTIFS(Type:Type, "Risk", Status:Status, "Open")

Cross Sheet

=COUNTIFS({Type}, "Risk", {Status}, "Open")