SOLVED

Counting rows that have specific critera choices in two drop boxes

%3CLINGO-SUB%20id%3D%22lingo-sub-2415947%22%20slang%3D%22en-US%22%3ECounting%20rows%20that%20have%20specific%20critera%20choices%20in%20two%20drop%20boxes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2415947%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20sheet%20that%20keeps%20track%20of%20whether%20the%20issues%20are%20Critical%2C%20High%2C%20Medium%2C%20and%20Low.%20I%20also%20keep%20track%20of%20whether%20these%20issues%20are%20Not%20started%2C%20In%20Progress%2C%20and%20Complete.%20I%20created%20a%20dashboard%20on%20another%20tab%20that%20notes%20by%20month%20how%20many%20are%20Not%20Started%2C%20Complete%20or%20In%20Progress.%20I%20want%20to%20also%20keep%20track%20of%20which%20Critical%20and%20High%20issues%20are%20Not%20Started%20or%20In%20Progress.%20In%20sum%20counting%20two%20drop%20down%20boxes%20in%20any%20row%20that%20have%20the%20specific%20criteria%20(i.e.%2C%20Critical%20and%20Not%20started%2C%20Critical%20and%20In%20Progress%20or%20High%20and%20Not%20Started%20and%20High%20and%20In%20Progress%20).%20I've%20tried%20several%20solutions%2C%20but%20am%20unable%20to%20find%20a%20formula%20that%20works.%20Please%20advise.%3C%2FP%3E%3CP%3EThe%20formula%20that%20works%20for%20counting%20just%20one%20drop%20down%20box%20is%3A%20%3DCOUNTIF('2021'!M1528%3AM1800%2C%22in%20progress%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2415947%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2415969%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20rows%20that%20have%20specific%20critera%20choices%20in%20two%20drop%20boxes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2415969%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1069575%22%20target%3D%22_blank%22%3E%40dmbeach24%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20the%20COUNTIFS%20function%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTIFS('2021'!M1528%3AM1800%2C%22in%20progress%22%2C'2021'!N1528%3AN1800%2C%22critical%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA%20pivot%20table%20would%20be%20a%20good%20way%20to%20count%20the%20number%20of%20occurrences%20of%20all%20combinations%3A%20add%20the%20Not%20Started%2FIn%20Progress%2FComplete%20column%2Ffield%20to%20the%20Rows%20area%2C%20the%20Critical%2FHigh%2FMedium%2FLow%20column%2Ffield%20to%20the%20Columns%20area%2C%20and%20either%20of%20these%20to%20the%20Values%20area.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2416102%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20rows%20that%20have%20specific%20critera%20choices%20in%20two%20drop%20boxes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2416102%22%20slang%3D%22en-US%22%3EThank%20you!%20It%20works!%3C%2FLINGO-BODY%3E
New Contributor

I have a sheet that keeps track of whether the issues are Critical, High, Medium, and Low. I also keep track of whether these issues are Not started, In Progress, and Complete. I created a dashboard on another tab that notes by month how many are Not Started, Complete or In Progress. I want to also keep track of which Critical and High issues are Not Started or In Progress. In sum counting two drop down boxes in any row that have the specific criteria (i.e., Critical and Not started, Critical and In Progress or High and Not Started and High and In Progress ). I've tried several solutions, but am unable to find a formula that works. Please advise.

The formula that works for counting just one drop down box is: =COUNTIF('2021'!M1528:M1800,"in progress")

2 Replies
best response confirmed by dmbeach24 (New Contributor)
Solution

@dmbeach24 

Perhaps the COUNTIFS function:

 

=COUNTIFS('2021'!M1528:M1800,"in progress",'2021'!N1528:N1800,"critical")

 

A pivot table would be a good way to count the number of occurrences of all combinations: add the Not Started/In Progress/Complete column/field to the Rows area, the Critical/High/Medium/Low column/field to the Columns area, and either of these to the Values area.