[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Group dates by month without Pivot Table

Hi guys

I have a spreadsheet with dates and I'd like to count how many customers joined in which month,  without the use of Pivot Tables.
Any ideas how you'd solve this?

Thanks
Massimo

Customers.xlsx
Dates.JPG
0
Massimo Scola
Asked:
Massimo Scola
  • 3
  • 3
2 Solutions
 
StephenJRCommented:
If I understand correctly, this array formula in E2 and down:

=SUM(IF(Einakufsdatum<>"",IF(MONTH(Einakufsdatum)=ROW()-1,1,0)))

Use Ctrl+Shift+Enter to enter formula.

What's wrong with PTs though?
0
 
jppintoCommented:
You can do it with a formula like this:

=SUMPRODUCT((MONTH($A$2:$A$14)=1)*(YEAR($A$2:$A$14)=2011))

Please take a look at the attached file to see how it works.

jppinto
Count-Months.xlsx
0
 
Massimo ScolaAuthor Commented:
@Stephen: I tried your formula but it doesn't work. I get an error message. Regarding the PTs. My boss asked me not to use PT

@jppinto: I tried your formula but it seems that it doesn't work if there are spaces in between the rows. These empty spaces are the dates when I don't know/remember when the customer was added to the spreadsheet. Or is the error message caused because I use a named range? #WERT = #VALUE

massimo
Copy-of-Dates.JPG
Copy-of-Count-Months.xlsx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
StephenJRCommented:
massimo - did you use Ctrl+Shift+Enter so that curly brackets appear round the formula?
0
 
Massimo ScolaAuthor Commented:
hi Stephen

Yes, I did press Ctrl+Shift+Enter but it doesn't work. I attached the spreadsheet to my previous post.
0
 
StephenJRCommented:
massimo - that was jppinto's formula (I think the problem is with those blanks). I tried mine and it works

=SUM(IF(Kunde_seit<>"",IF(MONTH(Kunde_seit)=ROW()-1,1,0)))

0
 
Massimo ScolaAuthor Commented:
Yes, now it works.
Thanks a lot guys.
I wish I could give you more points  :-/

Have a nice day

Massimo
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now