Microsoft Excel; Two conditions have to be true then be counted
- by Chris Jones
I'm working on a spreadsheet that two conditions have to true in order to be counted. If the month is January, and the number next to it is less than or equal to 30, then it's counted. Same rule applies for all the other months.
Thus far, I have:
=COUNTIFS(Sheet1!D2:D7,(SUMPRODUCT(--(MONTH(D2:D7)=1))),Sheet1!E2:E7,(COUNTIFS(E2:E7,"<=30")))
For example:
Column D
Jan 1, 2014
Feb 3, 2014
Feb 16, 2014
Mar 5, 2014
Mar 13, 2014
Mar 29, 2014
Column E
37
25
30
31
1
16
Outcome
Jan 0
Feb 2
Mar 2