Solved

# Group dates by month without Pivot Table

Posted on 2011-10-13
216 Views
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
Question by:mscola

LVL 24

Expert Comment

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

LVL 33

Assisted Solution

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

Author Comment

@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

LVL 24

Expert Comment

massimo - did you use Ctrl+Shift+Enter so that curly brackets appear round the formula?
0

Author Comment

hi Stephen

Yes, I did press Ctrl+Shift+Enter but it doesn't work. I attached the spreadsheet to my previous post.
0

LVL 24

Accepted Solution

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

Author Closing Comment

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

Have a nice day

Massimo
0

## Featured Post

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.