If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Counting
The formulas were not working consistently because some of the cells that
appeared to contain blanks actually had spaces in them and therefore were not seen as blank by excel. Once I cleared all empty cells of potential spaces, etc. the =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869"")) worked like a champ. Thanks for your help. It's thrilling when you finally figure out what was causing the problem. "Dave Peterson" wrote: =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 |
#22
|
|||
|
|||
Counting
Well, thank goodness for that! I'm glad that you finally achieved what
you wanted. Pete On Jul 10, 4:01*am, Civette wrote: I took a rest and dove back in. I found the problem. *I figured since I was not getting consistent results it meant that some of the cells were truly not "blank" even though they appeared so. *So, I went through the worksheet, verified all blank cells were truly blank by deleting any unseen contents and viola! the formula's worked and worked consistently. * Such a simple thing, that I've burned hours playing with, but now that I've done it I feel like I really accomplished something. Once again, thank you so much for your help. * "Pete_UK" wrote: The second term in the formula basically means cells in column AC are not empty - could it be that you have some cells with spaces in them (and therefore look empty), but Excel does not count them as such? Note also that if you adjust the range for column E, you must make the range for column AC the same. Merged cells can mess up many formulae - do you have any in the ranges covered by the formula? Pete "Civette" wrote in message ... I'm back to report on "partial success" . I tried all of the suggestions provided in both threads and tried many variations of said suggestions. The following produced the best results,but when I did a manual check the answere generated by the formula is not *consistent with the data. =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869"")) For instance I am expecting the formula to return an answer of "28" and in 4 out of 5 instances *(using a different value for the E ranges) it undercount. The bigger the "E" data pool the bigger the difference between what the answer should be and what the formula returned. *In 1 instance it was spot on but the E data pool was small (25 records). I've manipulated how it serches the date field and see no variation. *But, I still believe my problem my be in the date field because the "1247.24" field gives me a consistent number whether I use"1247.24" or follow Biff's suggestion of using a cell to hold the criteria. *I tried using a cell to hold the date criteria and the formula returns a correct answer becuase it searches for a specific date. Unfortunately, I don't care about a specific date, I want to pickup any cell in the second range that has a date. The only thing I can think of is that for some reason the date fields that should be counted are not being counted, but I stumped as to why. *Could merged cells impact the formula? *Could hard spaces or blank lines within the cell, before the date inpact the formula? "Pete_UK" wrote: Well, that's good to hear - thanks for feeding back. Can you set our minds to rest, though, and tell us what you did - what was wrong with the data that made all those suggested formulae not work? Pete "Civette" wrote in message ... 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"))- Hide quoted text - - Show quoted text - |
Thread Tools | |
Display Modes | |
|
|