Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF doesn't work | Excel Discussion (Misc queries) | |||
How to get Countif to work this out? | Excel Worksheet Functions | |||
Countif does not work | Excel Worksheet Functions | |||
Countif + vLookup: Can they work together? | Excel Discussion (Misc queries) | |||
COUNTIF doesnt work! | Excel Worksheet Functions |