Excel Help

[slickp007]

Hi

I have a work sheet like below:

A B
STATUS Date
Bound 01/01/2008
Bound 02/01/2008
Unbound 01/01/2008
Bound 01/02/2008
Unbound 01/01/2008

I want to count the number of bounds where the month on the date = 01. But i'm having a few problems. I thought this might work:

=COUNT(IF(Jan!A:A="Bound",IF(MONTH(Jan!B:B)=1,Jan!B:B))

But it doesn't, returns a value of 1, any help please?

 

[macropod]

Hi slickp007,

This is an array formula. Did you confrm it via Ctrl-Shift-Enter? If you don't, your count will never exceed 1.

As an alternative you could try this array formula:
=SUM(IF(Jan!A:A="Bound",IF(MONTH(Jan!B:B)=1,1)))

Note:

[MS MVP - Word]

 

[slickp007]

When i tried your version i confirmed it using the CTRL-Shift-Enter as you said and i get a number error. When i go to show calculation steps it says that it has a problem with the Jan!A:A="Bound" part.

When i confirmed my formula from the first post it just stays on 1, when i know there are more.

Any help please?

 

[AnotherHiggins]

A better way to look for multiple criteria is with the built in SumProduct.

[tab]=SUMPRODUCT((A2:A10 = "Bound") * (MONTH(B2:B10) = 1))

You can just use Enter with this function.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

A larger concern, though, is the fact that you have a sheet named "Jan". That suggests that you have your data chopped up by month.

If that is the case, I strongly urge you to reformat your data storage into a standardized table on a single sheet. That will make your life much, much easier in the long run.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[slickp007]

Cheers for the help mate and the speedy response

 

[SkipVought]

Slick,

I would concur and reemphasize the last paragraph that John offered to you.

Unfortunately, due to lack of knowledge regarding properly structured data, many spreadsheet users fall into the trap of storing data in a structure that they want to ultimately use or view the data. If the data is chopped up, it will be extremely difficult to analyze and manipulate, as it randers much of Excel's native functionality virtually useless.

Post back if you'd like help moving toward a better product.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[macropod]

Hi slickp007,

The most likely reason you got an error with the modified version of the formula I posted is that whole-column ranges can only be specified with Excel 2007; earlier versions need a specific range or rows to be defined.

Cheers

[MS MVP - Word]