Link to home
Start Free TrialLog in
Avatar of Robert Casaletta
Robert CasalettaFlag for United States of America

asked on

Simplifying Excel Formula: COUNTIFS Formula

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
ASKER CERTIFIED SOLUTION
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
[hmm I think hgholt may have simplified my text by copyable example - I type slow, you need not]
Avatar of Robert Casaletta

ASKER

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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial