CODEWITHSUNDEEP
excelexcel-formula
tcasteel
tcasteel

Reputation: 1

Formula to count Text and dates by multiple criteria

I have been trying to create a formula that will count the number of occurrences based on multiple criteria. In the display below I am looking to populate cell C5 with the number of rows that meet a given criteria. In this case, when the start date is between C1 and C2 and the team is Green or Blue or the Department is WAZ. I have been able to get counts to work based on just the date or just the team but have been unable to find a solution with all 3.

Countifs is what I have been trying but without success. I can only get a portion of the criteria to work. As soon as I add in the date part it errors out. 

Start   12/21/14     12/28/14     1/4/15
end     12/27/14     1/3/15       1/10/15



Project ID      start date     end date  team  Department   
1               1/7/15          6/26/15  Blue    SRT
2               12/27/14         1/23/18  Green   DFT
3               1/8/15           3/20/15  Red     DFT
4               1/3/15           6/20/15  Red     WAZ
5               12/29/14         7/12/15  Blue    DFT

enter image description here

Upvotes: 0

Views: 153

Answers (1)

Marcel
Marcel

Reputation: 2794

Supposing that you have the following criteria:
J5 = red
J6 = DFT
and your are looking for dates (start & end) in C1 and C2.
I am using my example, please follow the data in the image below:

enter image description here

so put this formula in cell C3, which shows the number of rows for your query:

=COUNTIFS($C$5:$C$11,">="&C1,$D$5:$D$11,"<="&C2,E5:E11,J5,F5:F11,J6)


as you see the formula yields 2 because there are only two rows that match these conditions. (the rows are in bold, rows 6 and 7).


So basically a countifs can contain several criteria:
COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, criteria_range3, criteria3 ...)

Upvotes: 0

Related Questions