Robert Casaletta
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 ")+(COUNTI FS($A$2:$A $22,"1/1/2 014",$B$2: $B$22,"2/1 /2014")+(C OUNTIFS($A $2:$A$22," 1/1/2014", $B$2:$B$22 ,"3/1/2014 ")+(COUNTI FS($A$2:$A $22,"2/1/2 014",$B$2: $B$22,"2/1 /2014")+(C OUNTIFS($A $2:$A$22," 2/1/2014", $B$2:$B$22 ,"3/1/2014 ")+(COUNTI FS($A$2:$A $22,"3/1/2 014",$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
=SUM(COUNTIFS($A$2:$A$22,"
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
[hmm I think hgholt may have simplified my text by copyable example - I type slow, you need not]
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.