Searching for neg. #'s on different sheets

[banchee]

I am currently working on a project that has 5 sheets, each sheet is the
same format. I am wanting to create a sheet that will look at each of
the 5 sheets and tell me how many negative numbers are on each sheet.
For example: I want it to tell me: Sheet 1 has 5 neg. numbers, Sheet
2 has 3 neg. #'s, so on. Any help would be GREATLY APPRECIATED!!
thanks for your time.

Kyle

 

[J.E. McGimpsey]

One way:

="Sheet 1 has " & COUNTIF(Sheet1!1:65536,"<0") & " neg. numbers"
="Sheet 2 has " & COUNTIF(Sheet2!1:65536,"<0") & " neg. numbers"
....

 

[Cliff Myers]

Say in your new sheet you choose cell A1, enter this code:
=Countif(Sheet1!A1:I1000,"<0") What that will do is count the number of
cells with a value less than zero in it on sheet1 of your workbook and
display the result in cell A1 of your new sheet. Do the same for the other
sheets in your workbook.

HTH

 

[Tom Ogilvy]

=countif(Sheet1!A1:Z5000,"<0")

change the A1:Z5000 to reflect the range you expect to contain information.

If you want text with it

="Sheet1 has " & countif(Sheet1!A1:Z5000,"<0") & " neg. numbers"

if you want all five sheets reported in 1 cell, then concatenate

="Sheet1 has " & countif(Sheet1!A1:Z5000,"<0") & " neg. numbers" & ", Sheet2
has " & countif(Sheet2!A1:Z5000,"<0") & " neg. numbers"