CODEWITHSUNDEEP
excelexcel-formula
Tim Wilkinson
Tim Wilkinson

Reputation: 3801

COUNTIF by month for range

I have 3 columns in excel that track when someone answered a call by date. Each person is called up to 3 times, hence the 3 columns. I am trying to count the number of people that answered in July, so the first two columns could be pre-July, or the first column could be in July. My data looks a little like this.

      A          B          C          D
1  1st Call   2nd Call   3rd Call     July
2  01/06/15   12/06/15   22/06/15     No
3  01/06/15   15/06/15   02/07/15     Yes
4  14/06/15   02/07/15                Yes
5  14/06/15   03/07/15                Yes
6  05/07/15                           Yes

So regardless of whether they answered after 1, 2, or 3 attempts, if any of the 3 columns is in July it returns "Yes".

I have tried this formula,

=IF(A2="", "", IF(COUNTIF(A2:C2, month=7)>0, "Yes", "No"))

and

=IF(A2="", "", IF(COUNTIF(A2:C2, MONTH(A2:C2)=7)>0, "Yes", "No"))

But to no avail. Does anyone know how to achieve this?

EDIT

I have realised since I can use the following,

=IF(A2="","", IF(MONTH(A2)=7, "Yes", IF(MONTH(B2)=7, "Yes", IF(MONTH(C2)=7, "Yes", "No"))))

However i'm still curious if my first attempt is achievable.

Upvotes: 0

Views: 895

Answers (2)

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3823

Yes, you can do what you're attempting with a simple tweak to how you are declaring your 'array' of possible responses:

=OR(IF(A2="", "", IF(Month(A2:C2))=7, TRUE, FALSE)))

You will need to confirm this with CTRL + SHIFT + ENTER, rather than just ENTER. It will work as you are intuiting, by running over each cell with the formula If(Month(CELL)>0, TRUE, FALSE). It then gives us an array of responses, depending on the results. [something like {TRUE, FALSE, TRUE}].

Note that I needed to change "yes" and "no" to TRUE / FALSE, because then we can wrap the whole thing in an OR statement. Otherwise, it's hard to pick out from your array of "yes"'s and "no"'s whether any of them are "yes". You can, if you need to, wrap this whole thing in an IF formula to convert TRUE to "yes", like so:

=IF(FORMULA ABOVE,"yes","no")

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

Why not just use an Or statement? =if(or(month(a2)=7,month(b2)=7,month(c2)=7),"Yes","No"). Then, you can use a countif to count how many "Yes" answers you have.

As for whether your first method would work, AFAIK it won't as the array in the formula won't work. Also, it's a little "overkill" to use an if countif > 0, then ... since all you want to know is if ANY of them are July, you could just use the above Or() statement.

Upvotes: 1

Related Questions