[Excel] – Countif Formula

Last update on November 2, 2009 09:00 AM by aakai1056
Published by aakai1056

[Excel] – Countif Formula




Issue



I have one excel file in that Column "B" has the "pending or Closed" Status and Column "C" has "Severity 1, Severity 2 and Severity 3"

I want to count in one cell with "Pending and Severity 2 tickets", please let me know which formula can help me.


I am able to count below two formulas individually but I want to know how many Pending Severity 1 count.
=COUNTIF(B:B,"Pending")
=COUNTIF(C:C,"Sevirity 1")

Solution


Suppose your data is like this:

A B C
1 4
2 5
3 6
4 7
5 8
pending severity 1
7 1
8 2
pending severity 2
9 4


In any empty cell use this formula, modify the formula to suit you. The spellings must be exact. So copy paste: 


=SUMPRODUCT((B1:B10="pending")*(C1:C10="severity 1"))

Note


Thanks to venkat1926 for this tip on the forum.
Best answers for « – Countif Formula » in :
[Excel]changing cell formula to text Show [Excel]changing cell formula to text Issue Solution Notes Issue Consider that I have: In cell A4, it contains a formula =Sum(A1:B3)+A3/B2. How to extract this formula in cell A6 as a string of text? That is ... I want cell A6...
Excel – Formula for cell calculation Show Excel – Formula for cell calculation Issue Solution Note Issue Simple formula of =J3-SUM(L3:X3) BUT if J3 is empty then I want the formula to run as =C3-SUM(L3:X3) What is the proper formula to get the calculation to utilize J3 if...
EXCEL: Nested IF formula question (Solved) Show
Basic Excel Formulas ShowBasic Excel Formulas Below are some basic formulas for Microsoft excel: Basic formula : ADDITION cell A1 to A10 = sum (A1: A10) AVERAGE cell A1 to A10 = average (A1: A10) MAXIMUM cell A1 to A10 = max (A1: A10) MINIMUM...
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
Excel - Extract identical cells of 2 columns ShowExcel - Extract identical cells of 2 columns Issue Solution Note Issue Hello, Got a list of words in column A Got a list of words in column B I am looking for a formula that will produce in column C a list of the words that...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Spreadsheet - Formulas ShowIntroduction to Formulas The main use of a spreadsheet is to automate calculations, which means using cells to perform operations based on values in other cells. The spreadsheet recalculates all the values each time a change is made to the value of...
Spreadsheets - Conditional expressions ShowWhat is a Conditional Structure? Conditional structures are instructions that allow to test if a condition is true or not. Conditional structures may be associated together. To successfully execute these tests using logical functions, spreadsheets...
They need your help