Simplifying Excel Formula: COUNTIFS Formula

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

=SUM(COUNTIFS($A$2:$A$22,"1/1/2014",$B$2:$B$22,"1/1/2014")+(COUNTIFS($A$2:$A$22,"1/1/2014",$B$2:$B$22,"2/1/2014")+(COUNTIFS($A$2:$A$22,"1/1/2014",$B$2:$B$22,"3/1/2014")+(COUNTIFS($A$2:$A$22,"2/1/2014",$B$2:$B$22,"2/1/2014")+(COUNTIFS($A$2:$A$22,"2/1/2014",$B$2:$B$22,"3/1/2014")+(COUNTIFS($A$2:$A$22,"3/1/2014",$B$2:$B$22,"3/1/2014")))))))

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.
EE-COUNTIFS.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Insert this in G3
=COUNTIFS($A$2:$A$22,"<="&G1,$B$2:$B$22,"<="&G1)
Copy to E3:F3.

For the Qual in G2
=COUNTIF($A$2:$A$22,G1)
Copy to E2:F2
Commented:
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 '<').

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

Author

Commented:
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