Archer Dynamics

When working in Excel, many people reach for COUNTIF to tally values, or worse yet, they manually count the cells! Ouch!
COUNTIF only works with one condition on one range. What if you need to count cells in one column based on criteria in another column and have two ranges to deal with, such as counting values in Column A, but only if Column B equals “Yes”?

Solution

Use COUNTIFS function in Excel with multiple criteria:

=COUNTIFS(B:B,"Yes",A:A,"<>")

Explanation

B:B,"Yes" → Only include rows where Column B equals Yes.
A:A,"<>" → Only count rows where Column A is not blank.

Note:
In Excel <> means “not equal to nothing, i.e. the cell is not blank”.
I know that some people like using LEN()>0 function to identify non-blank cells (it literally means: length is bigger than zero), but unfortunately, with COUNTIFS you cannot nest another formula inside it. For that you’d need to use SUMPRODUCT function!

Example:

Here we have a simple table with amounts in Column A and a Yes/No flag in Column B. If we want to count the number of donated items in column A, we’d need to filter Column B to “Yes” and then count the cells in A. Or, we could use COUNTIFS:

Anywhere in the spreadsheet we’d type the below formula and it would count only those cells in A where B was equal to “Yes”

=COUNTIFS(B:B,"Yes",A:A,"<>")

Notice that I have not hardcoded A2:A11 in the formula, but instead put an A:A “full column reference” in it, meaning the formula will look at the entire column A, all 1 million + rows, and thus being flexible — as I adjust the data and add more records, the formula recalculates itself on the fly. If I hardcoded it to A2:A11 and added more records later, the formula would be still reading only the 10 rows I originally told it to look at, ignoring the new data completely. This is very handy when you have spreadsheets you keep constantly working in and appending new data.

Variations

Count all rows where Column B = Yes:

excel

=COUNTIFS(B:B,"Yes")

Count rows where Column B = Yes and Column A > 100:

excel

=COUNTIFS(B:B,"Yes",A:A,">100")

Sum values in Column A where Column B = Yes:

excel

=SUMIFS(A:A,B:B,"Yes")

Need help streamlining your spreadsheets or solving tricky Excel problems? I offer quick one‑on‑one consultations to get you unstuck fast. Reach out today and let’s make your workflow smoother.