counting occurences by month

[thalpo]

Hi there,
how do I go by...

if A1 is blank then ignore if A1 is not blank then look up date in B1 and count by month in C1.

[Libertine]

Use the IF function with the COUNTBLANK function:

IF(logical_test,value_if_true,value_if_false)
COUNTBLANK(range)


Try the following line:

=IF(COUNTBLANK(A1) = 1, "Blank", "Not blank")

When implimenting "new" things for the first time I make the output a simple text, not perform another function. This tells me if I did it correctly or not, that way I am troubleshooting my problems 1 at a time, not 3 or 4.

Once you get that to work, replace the "Blank" (value_if_true) and "Not blank" (value_if_false) with the output or formulias you need.

[oldchippy]

Hi there,
how do I go by...

if A1 is blank then ignore if A1 is not blank then look up date in B1 and count by month in C1.

Hello thalpo,

When you say and count by month in C1, I'm assuming you want the number of the month from B1. If this is the case then put this into C1

=IF(A1="","",MONTH(B1))

oldchippy

[thalpo]

Hi again,

I think I did not explain it right. What I am trying to do is:

I put a book title in A1 and a start date when I started reading it in B1. Lets say I started 50 different books. Now need to find out how many books I started in which month. I need it to ignore A1 if I have not put a title in. I hope this makes more sence. thanks a million in advance.

[oldchippy]

Ok, try this

A1 type Title
B1 type Date
C1 type Month
A2 "Your Book Title"
B2 22/09/06
C2 = IF(A2="","",MONTH(B2))
Fill all the rows below as required, for now assuming 50 books.
Drag formula in C2 down 500 rows for now (assuming your not always reading - are you?)

Now go to Data > Pivot Table Report
Step 1 (accept defaults) and click next
Step 2 enter $A$1:$C$500
Step 4 click Layout...
Drag "Title" to Row and drop
Drag "Date" to Page and drop
Drag "Month" to Column and drop
Then "Month" again to Data
Double click on "Sum of Month", in Summarize box select "Count Nums"
OK, Finish

You should now have a Pivot Table on a new sheet with bars floating on your sheet. When you add more books to your list, to refresh you Pitot Table click the Refresh icon.

Let me know how you get on

oldchippy

[thalpo]

Hi there,

this works perfect. I did it w/o the pivot table....
but it always returns 1 if I only put the title in and leave the date empty. I might have the book and already input it but have not started reading yet. What you say?

[oldchippy]

Hi there,

this works perfect. I did it w/o the pivot table....
but it always returns 1 if I only put the title in and leave the date empty. I might have the book and already input it but have not started reading yet. What you say?

Hi,

Modified to display nothing if only one cell is filled in

=IF(OR(A1="",B1=""),"",MONTH(B1))

Have a good read

oldchippy

[thalpo]

Hello oldchippy,

thanks a million. That is exactly what I needed.