| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting
I have a spreadsheet containing thousands of records (each row is a different
record) and 20 different data columns. I want to count the number of records when the a specific number is in one column and a date (any date) appears in the second column. The column containing the dates may also contain blank cells, since this column is not filled in until some action triggers the need to put in a date. I've tried the following formula and get "0". Can someone help? =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869="01/01/2006")) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting
Try it this way:
=SUMPRODUCT((E4:E869=1247.24)*(AC4:AC869=--"01/01/2006")) You can't mix up text and numbers. Hope this helps. Pete "Civette" wrote in message ... I have a spreadsheet containing thousands of records (each row is a different record) and 20 different data columns. I want to count the number of records when the a specific number is in one column and a date (any date) appears in the second column. The column containing the dates may also contain blank cells, since this column is not filled in until some action triggers the need to put in a date. I've tried the following formula and get "0". Can someone help? =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869="01/01/2006")) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting
Tried, and it didn't work. Could I be getting snagged on some type of
formatting glitch? "Pete_UK" wrote: Try it this way: =SUMPRODUCT((E4:E869=1247.24)*(AC4:AC869=--"01/01/2006")) You can't mix up text and numbers. Hope this helps. Pete "Civette" wrote in message ... I have a spreadsheet containing thousands of records (each row is a different record) and 20 different data columns. I want to count the number of records when the a specific number is in one column and a date (any date) appears in the second column. The column containing the dates may also contain blank cells, since this column is not filled in until some action triggers the need to put in a date. I've tried the following formula and get "0". Can someone help? =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869="01/01/2006")) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting
Yes, it might be that your dates are text values that just look like dates,
or that the numbers in column E are not really numbers but text values also. I see in your comments to Biff that you are not searching for a specific date, so you might like to try these: =SUMPRODUCT((E4:E869=1247.24)*(AC4:AC869<"")) and: =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869<"")) to see which of them give you an answer other than zero. Hope this helps. Pete "Civette" wrote in message ... Tried, and it didn't work. Could I be getting snagged on some type of formatting glitch? "Pete_UK" wrote: Try it this way: =SUMPRODUCT((E4:E869=1247.24)*(AC4:AC869=--"01/01/2006")) You can't mix up text and numbers. Hope this helps. Pete "Civette" wrote in message ... I have a spreadsheet containing thousands of records (each row is a different record) and 20 different data columns. I want to count the number of records when the a specific number is in one column and a date (any date) appears in the second column. The column containing the dates may also contain blank cells, since this column is not filled in until some action triggers the need to put in a date. I've tried the following formula and get "0". Can someone help? =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869="01/01/2006")) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting
It might also be that the number that looks like 1247.24 is not actually
that value - if the cell is formatted to 2 dp then it could be any number between 1247.235 and 1247.2449999etc, so you might like to change the first condition to: (ROUND(E4:E869,2)=1247.24) Hope this helps. Pete "Pete_UK" wrote in message ... Yes, it might be that your dates are text values that just look like dates, or that the numbers in column E are not really numbers but text values also. I see in your comments to Biff that you are not searching for a specific date, so you might like to try these: =SUMPRODUCT((E4:E869=1247.24)*(AC4:AC869<"")) and: =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869<"")) to see which of them give you an answer other than zero. Hope this helps. Pete "Civette" wrote in message ... Tried, and it didn't work. Could I be getting snagged on some type of formatting glitch? "Pete_UK" wrote: Try it this way: =SUMPRODUCT((E4:E869=1247.24)*(AC4:AC869=--"01/01/2006")) You can't mix up text and numbers. Hope this helps. Pete "Civette" wrote in message ... I have a spreadsheet containing thousands of records (each row is a different record) and 20 different data columns. I want to count the number of records when the a specific number is in one column and a date (any date) appears in the second column. The column containing the dates may also contain blank cells, since this column is not filled in until some action triggers the need to put in a date. I've tried the following formula and get "0". Can someone help? =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869="01/01/2006")) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting
Still not working. I'm Stumped
"Pete_UK" wrote: It might also be that the number that looks like 1247.24 is not actually that value - if the cell is formatted to 2 dp then it could be any number between 1247.235 and 1247.2449999etc, so you might like to change the first condition to: (ROUND(E4:E869,2)=1247.24) Hope this helps. Pete "Pete_UK" wrote in message ... Yes, it might be that your dates are text values that just look like dates, or that the numbers in column E are not really numbers but text values also. I see in your comments to Biff that you are not searching for a specific date, so you might like to try these: =SUMPRODUCT((E4:E869=1247.24)*(AC4:AC869<"")) and: =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869<"")) to see which of them give you an answer other than zero. Hope this helps. Pete "Civette" wrote in message ... Tried, and it didn't work. Could I be getting snagged on some type of formatting glitch? "Pete_UK" wrote: Try it this way: =SUMPRODUCT((E4:E869=1247.24)*(AC4:AC869=--"01/01/2006")) You can't mix up text and numbers. Hope this helps. Pete "Civette" wrote in message ... I have a spreadsheet containing thousands of records (each row is a different record) and 20 different data columns. I want to count the number of records when the a specific number is in one column and a date (any date) appears in the second column. The column containing the dates may also contain blank cells, since this column is not filled in until some action triggers the need to put in a date. I've tried the following formula and get "0". Can someone help? =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869="01/01/2006")) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting
IT worked. Yeah. I did a bit of data manipulation and it's working. Thank
you all for your help "Pete_UK" wrote: It might also be that the number that looks like 1247.24 is not actually that value - if the cell is formatted to 2 dp then it could be any number between 1247.235 and 1247.2449999etc, so you might like to change the first condition to: (ROUND(E4:E869,2)=1247.24) Hope this helps. Pete "Pete_UK" wrote in message ... Yes, it might be that your dates are text values that just look like dates, or that the numbers in column E are not really numbers but text values also. I see in your comments to Biff that you are not searching for a specific date, so you might like to try these: =SUMPRODUCT((E4:E869=1247.24)*(AC4:AC869<"")) and: =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869<"")) to see which of them give you an answer other than zero. Hope this helps. Pete "Civette" wrote in message ... Tried, and it didn't work. Could I be getting snagged on some type of formatting glitch? "Pete_UK" wrote: Try it this way: =SUMPRODUCT((E4:E869=1247.24)*(AC4:AC869=--"01/01/2006")) You can't mix up text and numbers. Hope this helps. Pete "Civette" wrote in message ... I have a spreadsheet containing thousands of records (each row is a different record) and 20 different data columns. I want to count the number of records when the a specific number is in one column and a date (any date) appears in the second column. The column containing the dates may also contain blank cells, since this column is not filled in until some action triggers the need to put in a date. I've tried the following formula and get "0". Can someone help? =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869="01/01/2006")) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting
a date (any date) appears in the second column.
Hmmm.... You say "any date" but yet you're testing for a specific date criteria! Try it like this: =SUMPRODUCT(--(E4:E869=1247.24),--(AC4:AC869=DATE(2006,1,1))) Better to use cells to hold the criteria then you won't get messed up with quotation marks: A1 = 1247.24 B1 = 1/1/2006 =SUMPRODUCT(--(E4:E869=A1),--(AC4:AC869=B1)) -- Biff Microsoft Excel MVP "Civette" wrote in message ... I have a spreadsheet containing thousands of records (each row is a different record) and 20 different data columns. I want to count the number of records when the a specific number is in one column and a date (any date) appears in the second column. The column containing the dates may also contain blank cells, since this column is not filled in until some action triggers the need to put in a date. I've tried the following formula and get "0". Can someone help? =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869="01/01/2006")) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting
I originally tried using
=SUMPRODUCT((E4:E869="1247.24")*AC4:AC869="**/**/**")) but had no success. So, I tried a specific number since I knew that date met the criteria I was looking for...and, still I received a "0" . "T. Valko" wrote: a date (any date) appears in the second column. Hmmm.... You say "any date" but yet you're testing for a specific date criteria! Try it like this: =SUMPRODUCT(--(E4:E869=1247.24),--(AC4:AC869=DATE(2006,1,1))) Better to use cells to hold the criteria then you won't get messed up with quotation marks: A1 = 1247.24 B1 = 1/1/2006 =SUMPRODUCT(--(E4:E869=A1),--(AC4:AC869=B1)) -- Biff Microsoft Excel MVP "Civette" wrote in message ... I have a spreadsheet containing thousands of records (each row is a different record) and 20 different data columns. I want to count the number of records when the a specific number is in one column and a date (any date) appears in the second column. The column containing the dates may also contain blank cells, since this column is not filled in until some action triggers the need to put in a date. I've tried the following formula and get "0". Can someone help? =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869="01/01/2006")) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting
Ok, if you do want to count *any date* try it like this:
=SUMPRODUCT(--(E4:E869=1247.24),--(ISNUMBER(AC4:AC869))) Dates are really just numbers formatted to look like a date. -- Biff Microsoft Excel MVP "Civette" wrote in message ... I originally tried using =SUMPRODUCT((E4:E869="1247.24")*AC4:AC869="**/**/**")) but had no success. So, I tried a specific number since I knew that date met the criteria I was looking for...and, still I received a "0" . "T. Valko" wrote: a date (any date) appears in the second column. Hmmm.... You say "any date" but yet you're testing for a specific date criteria! Try it like this: =SUMPRODUCT(--(E4:E869=1247.24),--(AC4:AC869=DATE(2006,1,1))) Better to use cells to hold the criteria then you won't get messed up with quotation marks: A1 = 1247.24 B1 = 1/1/2006 =SUMPRODUCT(--(E4:E869=A1),--(AC4:AC869=B1)) -- Biff Microsoft Excel MVP "Civette" wrote in message ... I have a spreadsheet containing thousands of records (each row is a different record) and 20 different data columns. I want to count the number of records when the a specific number is in one column and a date (any date) appears in the second column. The column containing the dates may also contain blank cells, since this column is not filled in until some action triggers the need to put in a date. I've tried the following formula and get "0". Can someone help? =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869="01/01/2006")) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting
Didn't work.
I can successfully use =count(AC4:AC869) to get an accurate count of the number of dates in the column. Could we use something like that with the Sumproduct to achieve my count criteria? for instance =SUMPRODUCT(--(E4:E869=1247.24),COUNT(AC4:AC869))) "T. Valko" wrote: Ok, if you do want to count *any date* try it like this: =SUMPRODUCT(--(E4:E869=1247.24),--(ISNUMBER(AC4:AC869))) Dates are really just numbers formatted to look like a date. -- Biff Microsoft Excel MVP "Civette" wrote in message ... I originally tried using =SUMPRODUCT((E4:E869="1247.24")*AC4:AC869="**/**/**")) but had no success. So, I tried a specific number since I knew that date met the criteria I was looking for...and, still I received a "0" . "T. Valko" wrote: a date (any date) appears in the second column. Hmmm.... You say "any date" but yet you're testing for a specific date criteria! Try it like this: =SUMPRODUCT(--(E4:E869=1247.24),--(AC4:AC869=DATE(2006,1,1))) Better to use cells to hold the criteria then you won't get messed up with quotation marks: A1 = 1247.24 B1 = 1/1/2006 =SUMPRODUCT(--(E4:E869=A1),--(AC4:AC869=B1)) -- Biff Microsoft Excel MVP "Civette" wrote in message ... I have a spreadsheet containing thousands of records (each row is a different record) and 20 different data columns. I want to count the number of records when the a specific number is in one column and a date (any date) appears in the second column. The column containing the dates may also contain blank cells, since this column is not filled in until some action triggers the need to put in a date. I've tried the following formula and get "0". Can someone help? =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869="01/01/2006")) |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting
It doesn't look like you've tried Biff's first suggestion.
Use =date() when entering the date--not just a string. Civette wrote: I originally tried using =SUMPRODUCT((E4:E869="1247.24")*AC4:AC869="**/**/**")) but had no success. So, I tried a specific number since I knew that date met the criteria I was looking for...and, still I received a "0" . "T. Valko" wrote: a date (any date) appears in the second column. Hmmm.... You say "any date" but yet you're testing for a specific date criteria! Try it like this: =SUMPRODUCT(--(E4:E869=1247.24),--(AC4:AC869=DATE(2006,1,1))) Better to use cells to hold the criteria then you won't get messed up with quotation marks: A1 = 1247.24 B1 = 1/1/2006 =SUMPRODUCT(--(E4:E869=A1),--(AC4:AC869=B1)) -- Biff Microsoft Excel MVP "Civette" wrote in message ... I have a spreadsheet containing thousands of records (each row is a different record) and 20 different data columns. I want to count the number of records when the a specific number is in one column and a date (any date) appears in the second column. The column containing the dates may also contain blank cells, since this column is not filled in until some action triggers the need to put in a date. I've tried the following formula and get "0". Can someone help? =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869="01/01/2006")) -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting
=date() only works for a specific date. Unfortunately, I'm not interested in
a specific date, I am interested in all records that meet 2 criteria: column A =1247.24 and Column E contains a date. If you have any ideas, I'm open to suggestions. .."Dave Peterson" wrote: It doesn't look like you've tried Biff's first suggestion. Use =date() when entering the date--not just a string. Civette wrote: I originally tried using =SUMPRODUCT((E4:E869="1247.24")*AC4:AC869="**/**/**")) but had no success. So, I tried a specific number since I knew that date met the criteria I was looking for...and, still I received a "0" . "T. Valko" wrote: a date (any date) appears in the second column. Hmmm.... You say "any date" but yet you're testing for a specific date criteria! Try it like this: =SUMPRODUCT(--(E4:E869=1247.24),--(AC4:AC869=DATE(2006,1,1))) Better to use cells to hold the criteria then you won't get messed up with quotation marks: A1 = 1247.24 B1 = 1/1/2006 =SUMPRODUCT(--(E4:E869=A1),--(AC4:AC869=B1)) -- Biff Microsoft Excel MVP "Civette" wrote in message ... I have a spreadsheet containing thousands of records (each row is a different record) and 20 different data columns. I want to count the number of records when the a specific number is in one column and a date (any date) appears in the second column. The column containing the dates may also contain blank cells, since this column is not filled in until some action triggers the need to put in a date. I've tried the following formula and get "0". Can someone help? =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869="01/01/2006")) -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting
=SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869<""))
or =SUMPRODUCT((E4:E869="1247.24")*(isnumber(AC4:AC86 9))) If you have any text in ac4:ac869, then this won't give you the results you want. If you have any numeric entries in ac4:ac869 that are not dates, then this won't work. Civette wrote: =date() only works for a specific date. Unfortunately, I'm not interested in a specific date, I am interested in all records that meet 2 criteria: column A =1247.24 and Column E contains a date. If you have any ideas, I'm open to suggestions. ."Dave Peterson" wrote: It doesn't look like you've tried Biff's first suggestion. Use =date() when entering the date--not just a string. Civette wrote: I originally tried using =SUMPRODUCT((E4:E869="1247.24")*AC4:AC869="**/**/**")) but had no success. So, I tried a specific number since I knew that date met the criteria I was looking for...and, still I received a "0" . "T. Valko" wrote: a date (any date) appears in the second column. Hmmm.... You say "any date" but yet you're testing for a specific date criteria! Try it like this: =SUMPRODUCT(--(E4:E869=1247.24),--(AC4:AC869=DATE(2006,1,1))) Better to use cells to hold the criteria then you won't get messed up with quotation marks: A1 = 1247.24 B1 = 1/1/2006 =SUMPRODUCT(--(E4:E869=A1),--(AC4:AC869=B1)) -- Biff Microsoft Excel MVP "Civette" wrote in message ... I have a spreadsheet containing thousands of records (each row is a different record) and 20 different data columns. I want to count the number of records when the a specific number is in one column and a date (any date) appears in the second column. The column containing the dates may also contain blank cells, since this column is not filled in until some action triggers the need to put in a date. I've tried the following formula and get "0". Can someone help? =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869="01/01/2006")) -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| counting | Excel Discussion (Misc queries) | |||
| counting function but not double counting duplicates | Excel Worksheet Functions | |||
| Counting | Excel Worksheet Functions | |||
| Counting rows, then counting values. | Excel Discussion (Misc queries) | |||
| Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) | |||
Hybrid Mode
