Excel count

[crapshoot]

need help in excel. I am running Excel 2002 on Windows Vista. I am probably not going to phrase this right but the best way I can explain it is I am trying to countIF conditional
on multiple columns such as: if I have 500 rows and columns A through F all numeric, I want to count the number of times column B<27 and column D is >52 in the same row. Any help would be appreciated.

 

[MrKowz]

Welcome to the forums!

This requires a SUMPRODUCT formula on your version (which, btw, thanks for notifying us of your version! And I assume you mean 2000 or 2003? )

=SUMPRODUCT(--(B1:B500<27),--(D1:D500>52))

 

[crapshoot]

Thanks Mr.Kowz, I appreciate the excellent and quick reply, but now I am really confused. I tried your formula on a 133 row spreadsheet and came up with 64, it appeared correct, but I thought I would double check it by hand, I came up with 62, so I re-checked and still came up with 62 ... then the fun began, I ran a =count on the F column, all cells were filled numerically, so the answer should have been 133 ... it was 106. I would click a blank cell and press the formula bar expecting to see just the column and row in the range - just a count formula and I would get strange ranges like if I clicked F4 it would show something like C3:F4. I Earlier I was trying to learn Pivot Tables and added a row with labels on that same spreadsheet and saved it, when I started to have these problems I deleted that row ...did I mess something up by saving that Pivot table spreadsheet? I was also working with array's on the same sheet. To make things even more confusing, I ran a =count down a blank column expecting to see 0, I got 7, I would run a column with just one cell filled a 3 and I expected a 1, I got 4, my formula row was 4 and 3 was in the third row. is this as confusing to you as it is to me?

 

[crapshoot]

I found the problem. The version of Excel is 2002 (at least that is what is recorded in help)

The initial spreadsheet was made from dragging info from other workbooks, at the time some numeric data was entered as text and the flags were cleared by clicking ignore. When counting, that data was ignored so the count was wrong. Re-formating the data to numbers did not help ... I had to copy and paste the complete spreadsheet, the flags then appeared and now were clicked - change to number. Is there another way of doing this rather then copy and paste, since formating number did not work?