1

I'm trying to create a formula that'll tell me how many times a particular word in column B occurs per month. I've got the dates down column A.

For example, I would like to know how many time the word "car" appears in column B in February, March & April. With the dates (in dd/mm/yyyy format) down column A.

I've included an image to help illustrate

excel spreadsheet example

Iv'e scoured everywhere for a solution but to no avail so any help will be greatly appreciated.

Andrew Allan
  • 13
  • 4
  • You didn't research *that* hard because this is pretty simple stuff. (Homework, by chance?)... But I might help ya out if you answer fast: Are cells `D4:D6` **text** or are they dates (like `02/01/2018`) that are *formatted* to show only the month? ...and if it's not the second one, should it be? (or are February 15, 2017 and February 15, 2018 both supposed to be counted as the same *February*?) – ashleedawg Feb 17 '18 at 11:07
  • I thought I had tried hard! Yes, D4:D6 are just text, not formatted. And different dates in February are supposed to be counted as the same February. So E4 should have an answer of 8. Thank you for looking at it. – Andrew Allan Feb 17 '18 at 11:34
  • My questions above gave 2 different years, not just the same month.. :-) It's okay, we all gotta start somewhere, and here's as good as any! See my answer (below). – ashleedawg Feb 17 '18 at 11:36

1 Answers1

0

If cells D4:D6 are Text, you're best off to change them to Dates (like 02/01/2018), that use custom cell formatting of mmmm to display only the month. They will be easier to deal with for a number of reasons.

Then, "do this": (formulas below image)

screenshot example

Note that my Regional Settings are probably different from yours, so your dates will probably be entered as MM/DD/YYYY as opposed to YYYY-MM-DD.

Text from the image:

Cell D4 :   2018-02-01                                      

Cell D5 :   =EDATE(D4,1)                                        

"Fill" or Copy D5 into D6 etc…                                          

Cell E4 :   =COUNTIFS($A:$A,">="&$D4,$A:$A,"<"&EDATE($D4,1),$B:$B,E$3)                                      

"Fill" or Copy E4 into E4:G6

More Info:

EDIT:

If your "Month" columns must be text instead of the more logical "date-formatted-as-mmmm", then you'll need to use this formula instead:

=COUNTIFS($A:$A,">="&DATEVALUE($D4&" 1, " & YEAR($A$2)),$A:$A,"<"&EDATE(DATEVALUE($D4&" 1, " & YEAR($A$2)),1),$B:$B,E$3)

Notice how it's longer, more confusing, and since you're no longer specifying a year, it's assuming you want to use "the year of the date in cell A2", and may have other issues at some point... Unless it's homework, in which case it will be fine either way. :-)

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • Thank you so much for your help Ashlee. It works fantastically well! I shall endeavour to improve my homework should I encounter another problem in the future. – Andrew Allan Feb 17 '18 at 11:48
  • You're welcome. I added a 2nd formula that would work with Text instead of Dates in the "Month" column. hand in *both* alternatives and get some bonus points! – ashleedawg Feb 17 '18 at 11:56
  • @AndrewAllan -- Did you get it working? ... *cough, ahem* ... *darn sore throat tonight*... lol ... Hey, did you ever wonder about what the [`▲`](https://stackoverflow.com/a/48840826/8112776) symbol is for? [Here's a post about it.](https://meta.stackoverflow.com/a/269931) ☺ – ashleedawg Feb 17 '18 at 12:59
  • Yup, that works too. Tremendous. I have, I have! I've got a reputation less than 15, so it won't publicly display! – Andrew Allan Feb 17 '18 at 13:00
  • Oh yeah, forgot about that silly rule... I *think* if you click it now get it once you reach 15 rep. (It comes quick; you're up to 8 already, from this question.) – ashleedawg Feb 17 '18 at 13:06
  • Okey doke, I certainly will. you've been ever so helpful. – Andrew Allan Feb 17 '18 at 13:11
  • No problem, I'm just teasing about the points... The important thing is "paying it forward" when someone else has a question. – ashleedawg Feb 17 '18 at 13:14