Will countif work?

[Guest]

I have a result from my MYSQL querry into excel and I want to use the list of
dates to count against, I now want to create a summary sheet. I want to use
the date colums and just pull a count of how many rows have the August date,
September date and October date (these month results will be in different
cells)

 

[Guest]

This is a very simple approach. Say the dates are in column A. In B1 enter:
=TEXT(A1,"MMMM") and copy down. We see, for example:

10/25/2007 October

then its just:

=COUNTIF(B1:B500,"October")

 

[Don Guillett]

try
=sumproduct((month(a2:a22)=8)*1)

 

[Dave Peterson]

=sumproduct(--(isnumber(a1:a10),--(month(a1:a10)=8))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

========
If you were only interested in certain years:
=sumproduct(--(text(a1:a10,"yyyymm")="200708"))

 

[Dave Peterson]

Typo alert:
=sumproduct(--(isnumber(a1:a10),--(month(a1:a10)=8))
should have been:
=sumproduct(--(isnumber(a1:a10)),--(month(a1:a10)=8))

(I added a close paren.)

 

[Guest]

Here is the issue I am running into though, even though the columns are
formated to a date type this is how they are showing up when I am trying to
use the the syntax provided.

wait, I cant upload this screen shot, is there anyway I can display the issue?


--
Neall

Typo alert:
=sumproduct(--(isnumber(a1:a10),--(month(a1:a10)=8))
should have been:
=sumproduct(--(isnumber(a1:a10)),--(month(a1:a10)=8))

(I added a close paren.)

 

[Dave Peterson]

You could save the file to some web location (www.savefile.com is one). But
lots of people won't bother going to that other site to open a workbook that may
contain malicious code. I know I won't.

But formatting a cell as a date isn't enough to change the value in a cell.

You could check to see if any cell's value is a number (all real dates are
numbers to excel):

=isnumber(a1)
will return True if the cell contains a date. It'll return False if the value
is text.

Depending on what's in the cell, there may be ways to convert the text values to
real dates.

It could be as simple as selecting the range to fix
edit|Replace
what: / (slash)
with: / (slash)
replace all

or selecting the column and doing Data|Text to columns

But it depends on what's in the cell and maybe what your windows short date
format is.

Here is the issue I am running into though, even though the columns are
formated to a date type this is how they are showing up when I am trying to
use the the syntax provided.

wait, I cant upload this screen shot, is there anyway I can display the issue?

--
Neall

Typo alert:
=sumproduct(--(isnumber(a1:a10),--(month(a1:a10)=8))
should have been:
=sumproduct(--(isnumber(a1:a10)),--(month(a1:a10)=8))

(I added a close paren.)