Simplifying Excel Formula: COUNTIFS Formula

Robert Casaletta
Robert Casaletta used Ask the Experts™
Is there a simpler way to write the following:


The attached is a sample of my spreadsheet. I have dates where people become qualified (QUAL MON) to perform certain tasks and when they are completed (COMP MON). I need to track the number of completed tasks.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Insert this in G3
Copy to E3:F3.

For the Qual in G2
Copy to E2:F2
1) rather than using hardcoded dates you can use column title of date.
2)  rather testing the columns separately, combine them (ex ($A$2:$B$22)
3) consider changing from testing equality to testing for equal-or less than. Consideration for representation of date could apply (more numerical than textual for a '<').

[hmm I think hgholt may have simplified my text by copyable example - I type slow, you need not]
Robert CasalettaManager, Warranty Performance


Thank you - I had not thought of using the date in row 1; I noticed, for example, that you used "<="&G1...where can I learn more about that type of syntax and how to use it? Is the '&' the key? Also, can you explain a little more on point 2: "...combine them..." Do I use for both the range and criteria?
Criteria for Countif and Sumif must be text, except when equal like the Qual formula.
I think the best way to learn it is by doing.
It can sometimes be a little tricky to make, especially when working with dates.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial