Hi Everyone,
I am trying find a formula that will count the number of cells that contain a date given a certain criterion. The problem is that the criteria are in a different row and column than some of the cells I need to count. Here is what I'm looking for:
<tbody>
</tbody>
Where the formula determining the number of February and March wins for each person is as follows:
=COUNTIFS($A:$A,E3,$B:$B,">1/31/18",$B:$B,"<3/1/18")
As you can see, Weasley and Potter each had multiple wins in one month, but the formula will only count the first win because the second value is in a different row and column than the criterion in column A. I have tried using offset and a few variations of array formulas to no avail. Any suggestions?
Thanks,
Linda
I am trying find a formula that will count the number of cells that contain a date given a certain criterion. The problem is that the criteria are in a different row and column than some of the cells I need to count. Here is what I'm looking for:
February Wins | March Wins | |||||||
Name | Win Date | |||||||
Potter | 1 | Potter | 1 | |||||
Potter | 1/1/2018 | Weasley | 1 | Weasley | 0 | |||
Granger | 1 | Granger | 1 | |||||
Weasley | 1/3/2018 | Lovegood | 1 | Lovegood | 0 | |||
Longbottom | 0 | Longbottom | 1 | |||||
Potter | 2/7/2018 | |||||||
Granger | 2/13/2018 | |||||||
Lovegood | 2/1/2018 | |||||||
Weasley | 2/7/2018 | |||||||
2/13/2018 | ||||||||
Granger | 3/2/2018 | |||||||
Potter | 3/1/2018 | |||||||
3/28/2018 | ||||||||
Longbottom | 3/7/2018 |
<tbody>
</tbody>
Where the formula determining the number of February and March wins for each person is as follows:
=COUNTIFS($A:$A,E3,$B:$B,">1/31/18",$B:$B,"<3/1/18")
As you can see, Weasley and Potter each had multiple wins in one month, but the formula will only count the first win because the second value is in a different row and column than the criterion in column A. I have tried using offset and a few variations of array formulas to no avail. Any suggestions?
Thanks,
Linda