Will countif work?
 

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)
--
Neall

Will countif work?
 

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")

--
Gary''s Student - gsnu200751


"Neall" wrote:

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)
--
Neall

Will countif work?
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Neall" wrote in message
...
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)
--
Neall

Will countif work?
 

=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 he
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"))

Neall wrote:

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)
--
Neall

--

Dave Peterson

Will countif work?
 

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 wrote:

=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 he
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"))

Neall wrote:

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)
--
Neall

--

Dave Peterson

--

Dave Peterson

Will countif work?
 

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


"Dave Peterson" wrote:

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 wrote:

=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 he
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"))

Neall wrote:

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)
--
Neall

--

Dave Peterson

--

Dave Peterson

Will countif work?
 

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.

Neall wrote:

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

"Dave Peterson" wrote:

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 wrote:

=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 he
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"))

Neall wrote:

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)
--
Neall

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson