Excel - number of birthdays per month by gender 3

[craigey]

Hi

I'm trying to display the number of birthdays per month for a list of people which is split by gender. This is so the people using this know how many cards / present to buy (for a cancer charity) & if they should have racing cars or princesses (or other sterotypical themes). For example there might be 2 boys birthdays in January, but 4 girls birthdays and 3 of each in Feb etc, etc.

I know I can use a sumproduct to count the total number of birthdays per month by using 12 sumproduct queries (1 per month), but just can figure out how to get this to only sum if either a by / girl depending on the column. Sorry it's probably a really rubbish explanation. Hopefully this will help:

ColA, ColB, ColC, ColF, ColG, ColH
name, dob, gender, month, boys birthdays, girls birthdays

a, 1-2-10,boy, jan, 0, 1
b, 7-1-09,girl, feb, 2, 0
c, 8-3-09,girl, mar, 0, 1
d, 6-2-09,boy, apr, 0, 0

Thanks in advance

 

[judgeh59]

I may be missing something but it seems like a job for a Pivot Table...I took your data set...added some labels to the 1st row...labeled Month to Period, Boys b-day to Boys, Girls b-day to Girls. Row value - Period, data- Sum of Boys and Sum of Girls...you can make a dynamic table and do all sorts of things...

HTH

Ernest

Be Alert, America needs more lerts

 

[craigey]

I did attempt to use a pivot table, but the wizard just kept beating me into submission. I tried selecting colb & colc, then I read it needs 3 sets of data to work, so I tried with colb, colc & colg & it still wouldn't play nicely. I know it's something I'm not doing correctly, but I haven't done anything with pivot tables before.

Just realised the example above is a bit over simplified. columns a, b, &C would have loads records rows, but the colf, g & h would only have 12 rows one row for each month. My first post made it appear as though the last 3 colums would appear for each row.

 

[judgeh59]

yup, that does make a bit of difference...but you really only need the two columns, gender and the month of the their B-day...which you can write a little formula to create...then in your PT.

Row Label - Period (Created from the B-day)
Column label - Gender
Data LAbel - Count of Period...

also, what version of Excel are you running?

Ernest

Be Alert, America needs more lerts

 

[craigey]

at work 2007 at home 2003 (I think, but could be 2000 or xp). will give the pivot table another try

 

[SkipVought]

If you have REAL DATES, then you can put the date in either a COLUMN or ROW field and then GROUP on that field, and select MONTH. All you data will be summarized by the MONTH of the date.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

I am with judgeh59: "you really only need the two columns, gender and the month of the their B-day"

And would VBA solution work for you?

I have just 2 columns:
[tt]
A B C
DOB GENDER
1/15/2010 boy
7/29/2001 boy
10/28/2003 girl
7/15/2004 boy
10/3/2004 girl
10/17/2000 girl
1/13/2004 girl
1/24/2005 girl
10/1/2003 boy[/tt]

I formatted column B as Date, column C as Text

Sub GirlsBoysBDays()
Dim MyAry(0 To 1, 1 To 12) As Integer
Dim r As Integer
Dim m As Integer
Dim str As String

r = 2

Do While Range("B" & r).Value <> ""
    m = Month(CDate(Range("B" & r).Value))

    Select Case UCase(Range("C" & r).Value)
        Case "GIRL"
            MyAry(0, m) = MyAry(0, m) + 1
        Case "BOY"
            MyAry(1, m) = MyAry(1, m) + 1
    End Select

    r = r + 1
Loop

For r = 1 To 12
    If MyAry(0, r) > 0 Then
        str = str & MonthName(r) & _
        " -- Girl's B-Days " & _
        MyAry(0, r) & vbNewLine
    End If

    If MyAry(1, r) > 0 Then
        str = str & MonthName(r) & _
        " -- Boy's B-Days " & _
        MyAry(1, r) & vbNewLine
    End If
Next r

MsgBox str

End Sub

Paste this code in your VBA editor, run it and you should get the message box with
[tt]
January -- Girl's B-Days 2
January -- Boy's B-Days 1
July -- Boy's B-Days 2
October -- Girl's B-Days 3
October -- Boy's B-Days 1
[/tt]



Have fun.

---- Andy

 

[craigey]

Thanks I managed to get the Pivot table working by adding a column of Birth month & using that & gender in the pivot table.

I like the VBA solution too as it means I wouldn't need to add the extra column of birth month, which seems silly as the data is already in the dob field).

I would have thought that excel would allow the manipulation of the data in the pivot table like in a standard cell, but obviously not. (it didn't even like updating the value of the row label when I updated the original names of the months (changed from numbers to text - =TEXT(B2,"mmmm") ) instead it just used the orignal month numbers).

Thanks for your help guys.

 

[SkipVought]

I wouldn't need to add the extra column of birth month

As I stated earlier, if your dates are actual dates then you can GROUP your dates by MONTH in the PT. Takes about 15 seconds.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

listen to Skip! If you ever need to group by some other time-span apart from months, making the change will take less than 15sec if you go with the pivot-table and grouping approach.

Note: if you run into problems it almost always means there is a row somewhere that does not hold a valid date.

 

[craigey]

what does 'SkipVought' mean by actual dates?

The dob column is a date and is formatted as a Date (rather than text or general).

Can you provide an explanation of what options to select step by step to group by month in the pivot table.

 

[SkipVought]

what does 'SkipVought' mean by actual dates?

faq68-5827

If your date displays a NUMBER when you change the Number Format to GENERAL, then you have Real Dates.
[quogte]Can you provide an explanation of what options to select step by step to group by month in the pivot table. [/quote]
Right Click the Date Field in your Pivot Table, and select Group and in the GROUPING Window, select MONTH.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[judgeh59]

craigey - Skips grouping solution is much more elegant and quicker solution than mine...and again I learn from Skip...not only can you group months and other stuff but you can do multiple groups...so you don't need to create a month/year field to pivot on...which I end up doing a lot...you can group by year/month....most excellent...thanks again for TT...

Be Alert, America needs more lerts

 

[craigey]

Ok. I'm having difficulty producing the pivot table when using both the DOB column and the gender column. I keep getting a message saying that 'The PivotTable field name is not valid. To create a PivotTable Report, you must use data that is organised as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.'

When try to create the pivot table with just the dob column (which is using Real Dates) I managed to get the grouping by month on the dates to work. However as i didn't have the gender column (because of the error when selecting the DOB & Gender columns) I wasn't able to produce the results in the pivot table.

If I create a new column with birth month & use that & the gender column I can create a pivot table, but then don't have the need to perform grouping by month (which I'm guessing I can't do as it isn't a real date - just a duration).

 

[SkipVought]

Please post a sample of your source data table along with heading titles. Your table is displaying some abnormal anomaly.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]